MonthName Sort options

[klp via AccessMonster.com]

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

 

[Jeff Boyce]

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]

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?

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

 

[Jeff Boyce]

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
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?

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

 

[klp via 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!

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]

 

[Bob Howard]

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
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!

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

 

[klp via AccessMonster.com]

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......

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]

 

[Jeff Boyce]

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

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......

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

 

[Bob Howard]

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
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......

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

 

[klp via AccessMonster.com]

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?

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]