Query - Group by month & sort

[Jay]

Hi,

I have a table containing a date/time field. I have a summary query based
on this table which was set up using the wizard. I stipulated to group by
month when asked. (i.e. All records in April 2005)

The query works fine. However when I try and sort on the month field it
sorts based on the letter beginning the month i.e.

April 2005
February 2005
June 2005
May 2005

I want to output the query results in a report, which I will need in
ascending date order, not Date alpha order.

I don't know why it does this as the original date field is definitely a
date type & format etc.

Any help would be greatly appreciated.

-Jay-

 

[KARL DEWEY]

Open the query in design view and insert a column to the left of your present
Month-Year column. Use Format([YourDateField],"yyyymm") and uncheck the box
for display. It will be sorted correctly.

 

[Jay]

Thanks a lot for the response Karl. Will try it tomorrow.

Why does it do it? I notice that the summary Qry wizard inserted a
calculated field to do the grouping by field - is it this that somehow makes
Access deal with the months as text? Seems a bit odd when months will hardly
ever (if *at all*) need to be alpha sorted.

Cheers,
-Jay-

Open the query in design view and insert a column to the left of your present
Month-Year column. Use Format([YourDateField],"yyyymm") and uncheck the box
for display. It will be sorted correctly.

Hi,

I have a table containing a date/time field. I have a summary query based
on this table which was set up using the wizard. I stipulated to group by
month when asked. (i.e. All records in April 2005)

The query works fine. However when I try and sort on the month field it
sorts based on the letter beginning the month i.e.

April 2005
February 2005
June 2005
May 2005

I want to output the query results in a report, which I will need in
ascending date order, not Date alpha order.

I don't know why it does this as the original date field is definitely a
date type & format etc.

Any help would be greatly appreciated.

-Jay-

 

[Jay]

Oops, forgot to mention something. The original field is a full date (i.e.
12/04/2006, 23/07/2005. Do I add a new column to the left of the present
column? And just type the Format statement in the Field Row?

Sorry to be a bit slow.

Jay

Thanks a lot for the response Karl. Will try it tomorrow.

Why does it do it? I notice that the summary Qry wizard inserted a
calculated field to do the grouping by field - is it this that somehow makes
Access deal with the months as text? Seems a bit odd when months will hardly
ever (if *at all*) need to be alpha sorted.

Cheers,
-Jay-

Open the query in design view and insert a column to the left of your present
Month-Year column. Use Format([YourDateField],"yyyymm") and uncheck the box
for display. It will be sorted correctly.

Hi,

I have a table containing a date/time field. I have a summary query based
on this table which was set up using the wizard. I stipulated to group by
month when asked. (i.e. All records in April 2005)

The query works fine. However when I try and sort on the month field it
sorts based on the letter beginning the month i.e.

April 2005
February 2005
June 2005
May 2005

I want to output the query results in a report, which I will need in
ascending date order, not Date alpha order.

I don't know why it does this as the original date field is definitely a
date type & format etc.

Any help would be greatly appreciated.

-Jay-

 

[Larry Linson]

You are adding a Calculated Field, so in the Query Builder, you'd press
insert and then type "SomeName:" followed by the Format recommended by Karl.
Be sure to click the Sort and choose Ascending in the Query Builder -- just
adding the Field will not cause it to sort.

Larry Linson
Microsoft Access MVP

Oops, forgot to mention something. The original field is a full date
(i.e.
12/04/2006, 23/07/2005. Do I add a new column to the left of the present
column? And just type the Format statement in the Field Row?

Sorry to be a bit slow.

Jay

Thanks a lot for the response Karl. Will try it tomorrow.

Why does it do it? I notice that the summary Qry wizard inserted a
calculated field to do the grouping by field - is it this that somehow
makes
Access deal with the months as text? Seems a bit odd when months will
hardly
ever (if *at all*) need to be alpha sorted.

Cheers,
-Jay-

Open the query in design view and insert a column to the left of your
present
Month-Year column. Use Format([YourDateField],"yyyymm") and uncheck
the box
for display. It will be sorted correctly.

:

Hi,

I have a table containing a date/time field. I have a summary query
based
on this table which was set up using the wizard. I stipulated to group
by
month when asked. (i.e. All records in April 2005)

The query works fine. However when I try and sort on the month field
it
sorts based on the letter beginning the month i.e.

April 2005
February 2005
June 2005
May 2005

I want to output the query results in a report, which I will need in
ascending date order, not Date alpha order.

I don't know why it does this as the original date field is definitely
a
date type & format etc.

Any help would be greatly appreciated.

-Jay-