MonthName Sort options

I have a crosstab query that displays totals for each month. How do I convert
the month numbers to their actual name on my report? I used the monthName
function but it displays them in alphabetical order instead of chronilogical
order. Do I define this in my query or do I need to do it elsewhere?

Any Help greatful!

Kim P

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200708/1

Keep the sort by month number, add the month name. If you are building a
cross-tab query, you can define the cross-tab column names (e.g., "January",
"February", ...).

If you want to sort data in your report (and if you are using earlier
versions of Access), you'll need to use Sorting/Grouping in the report
definition to sort there (the query's sort doesn't carry through to the
report).

Regards

Jeff Boyce
Microsoft Office/Access MVP

"klp via AccessMonster.com" u19032@uwe wrote in message
news:76bce34154ca8@uwe...
I have a crosstab query that displays totals for each month. How do I
convert
the month numbers to their actual name on my report? I used the monthName
function but it displays them in alphabetical order instead of
chronilogical
order. Do I define this in my query or do I need to do it elsewhere?

Any Help greatful!

Kim P

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200708/1

I do have a crosstab query. Here are my fields:
ItemNo as row heading
ItemDesc as row heading
Months as column
Quantity as value
Date does not show but is a where statement for a date criteria

How do I define the columns as names when I already have the month as a
column. Do I need to write an if statement? New to the crosstab query, so I'm
not sure what to do. Standard query no problem.

I do need to sort in my report. I'm using Access 2003. If I use the
sorting/grouping I would sort by the month number right? But where would I
get my name from?

Jeff Boyce wrote:
Keep the sort by month number, add the month name. If you are building a
cross-tab query, you can define the cross-tab column names (e.g., "January",
"February", ...).

If you want to sort data in your report (and if you are using earlier
versions of Access), you'll need to use Sorting/Grouping in the report
definition to sort there (the query's sort doesn't carry through to the
report).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a crosstab query that displays totals for each month. How do I
convert
[quoted text clipped - 6 lines]

Kim P

--
Message posted via http://www.accessmonster.com

Perhaps we have a different concept of "cross-tab". In my mind, I was
imagining a query that listed the individual months across the top.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"klp via AccessMonster.com" u19032@uwe wrote in message
news:76c7c60d0b10a@uwe...
I do have a crosstab query. Here are my fields:
ItemNo as row heading
ItemDesc as row heading
Months as column
Quantity as value
Date does not show but is a where statement for a date criteria

How do I define the columns as names when I already have the month as a
column. Do I need to write an if statement? New to the crosstab query, so
I'm
not sure what to do. Standard query no problem.

I do need to sort in my report. I'm using Access 2003. If I use the
sorting/grouping I would sort by the month number right? But where would I
get my name from?

Jeff Boyce wrote:
Keep the sort by month number, add the month name. If you are building a
cross-tab query, you can define the cross-tab column names (e.g.,
"January",
"February", ...).

If you want to sort data in your report (and if you are using earlier
versions of Access), you'll need to use Sorting/Grouping in the report
definition to sort there (the query's sort doesn't carry through to the
report).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a crosstab query that displays totals for each month. How do I
convert
[quoted text clipped - 6 lines]

Kim P

--
Message posted via http://www.accessmonster.com

I'll try to clarify better. That's what I have. The month numbers appear in
my crosstab query as the column. So I will see 1-12 on my crosstab column.
Each representing the month respectively. But I want to see the actual month.
"Jan", "Feb","Mar", etc. I can do that by using the format or monthname
function. However, when I go to run my report it will not show up
chronilogical order, it shows it ascending. I do not want that. I have also
set the column properties in my crosstab, but that doesn't work correctly for
me because I have a rolling report. If the user puts in a date of 06/01/06-
05/31/07 it will need to start with the month of June and end with May. With
putting the names of the months in the column properties it won't roll. I
have my report running just how I need it except for the heads. I have a lot
of code behind the report to place the correct fields in their unbound text
boxes. I don't know if that's conflicting with it or not. I know I'm all over
the place here. So do I need to adjust my crosstab query to show the months
or do I need to perform that action in my report?

Thanks!

Jeff Boyce wrote:
Perhaps we have a different concept of "cross-tab". In my mind, I was
imagining a query that listed the individual months across the top.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I do have a crosstab query. Here are my fields:
ItemNo as row heading
[quoted text clipped - 32 lines]

Kim P

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200708/1

How about making the month name a visible column and the month number a
hidden column --- and sort on the month number.

"klp via AccessMonster.com" u19032@uwe wrote in message
news:76c9e8e167f06@uwe...
I'll try to clarify better. That's what I have. The month numbers appear
in
my crosstab query as the column. So I will see 1-12 on my crosstab column.
Each representing the month respectively. But I want to see the actual
month.
"Jan", "Feb","Mar", etc. I can do that by using the format or monthname
function. However, when I go to run my report it will not show up
chronilogical order, it shows it ascending. I do not want that. I have
also
set the column properties in my crosstab, but that doesn't work correctly
for
me because I have a rolling report. If the user puts in a date of
06/01/06-
05/31/07 it will need to start with the month of June and end with May.
With
putting the names of the months in the column properties it won't roll. I
have my report running just how I need it except for the heads. I have a
lot
of code behind the report to place the correct fields in their unbound
text
boxes. I don't know if that's conflicting with it or not. I know I'm all
over
the place here. So do I need to adjust my crosstab query to show the
months
or do I need to perform that action in my report?

Thanks!

Jeff Boyce wrote:
Perhaps we have a different concept of "cross-tab". In my mind, I was
imagining a query that listed the individual months across the top.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I do have a crosstab query. Here are my fields:
ItemNo as row heading
[quoted text clipped - 32 lines]

Kim P

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200708/1

Unless I have my crosstab query set up wrong. I've tried that, it doesn't
work. It doesn't make the months chornological it still puts them
alphabetical. I don't know what else to try. Any other suggestions? This my
layout for my query:

ItemNo - Row
Desc - Row
MonthName - column that displays month name
Qty - Value
TrxDate - not shown, but uses Where to determine date
MonthNum: Month([TrxDate]Not shown, sort ascending

My months still come up as Apr - Aug - Jul - Jun etc......

Bob Howard wrote:
How about making the month name a visible column and the month number a
hidden column --- and sort on the month number.

I'll try to clarify better. That's what I have. The month numbers appear
in
[quoted text clipped - 37 lines]

Kim P

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200708/1

Check Access HELP for particulars on using Crosstab Queries.

The Properties sheet for the crosstab query has a Columns property. You
might be able to enter the monthnames, in order, in that property.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"klp via AccessMonster.com" u19032@uwe wrote in message
news:76cad149afa1e@uwe...
Unless I have my crosstab query set up wrong. I've tried that, it doesn't
work. It doesn't make the months chornological it still puts them
alphabetical. I don't know what else to try. Any other suggestions? This
my
layout for my query:

ItemNo - Row
Desc - Row
MonthName - column that displays month name
Qty - Value
TrxDate - not shown, but uses Where to determine date
MonthNum: Month([TrxDate]Not shown, sort ascending

My months still come up as Apr - Aug - Jul - Jun etc......

Bob Howard wrote:
How about making the month name a visible column and the month number a
hidden column --- and sort on the month number.

I'll try to clarify better. That's what I have. The month numbers appear
in
[quoted text clipped - 37 lines]

Kim P

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200708/1

Another possibility is to ***define*** the column contents as a date, and
include the month, a day (like "01") and a year ('like "2007") in each
entry. But for the formatting of the column, specify only "mmm" so that
only the abbreviated alpha month appears. Bob

"klp via AccessMonster.com" u19032@uwe wrote in message
news:76cad149afa1e@uwe...
Unless I have my crosstab query set up wrong. I've tried that, it doesn't
work. It doesn't make the months chornological it still puts them
alphabetical. I don't know what else to try. Any other suggestions? This
my
layout for my query:

ItemNo - Row
Desc - Row
MonthName - column that displays month name
Qty - Value
TrxDate - not shown, but uses Where to determine date
MonthNum: Month([TrxDate]Not shown, sort ascending

My months still come up as Apr - Aug - Jul - Jun etc......

Bob Howard wrote:
How about making the month name a visible column and the month number a
hidden column --- and sort on the month number.

I'll try to clarify better. That's what I have. The month numbers appear
in
[quoted text clipped - 37 lines]

Kim P

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200708/1

Okay, a little confused on the last post. I have tried formatting "mmm" to
the field. Having no success at that. What do you mean by define the column
contents? Sorry I'm a bit confused. However, if I set the properties of the
column field as "jan","feb","mar" etc for each month. The report works fine.
BUT I need it to roll then. Instead it will show every single month starting
with Jan. I want it to show the month starting at the date I put in the start
date field and ending with the end date I put in. Is that workable?

Bob Howard wrote:
Another possibility is to ***define*** the column contents as a date, and
include the month, a day (like "01") and a year ('like "2007") in each
entry. But for the formatting of the column, specify only "mmm" so that
only the abbreviated alpha month appears. Bob

Unless I have my crosstab query set up wrong. I've tried that, it doesn't
work. It doesn't make the months chornological it still puts them
[quoted text clipped - 19 lines]

Kim P

--
Message posted via http://www.accessmonster.com