Sorting Using Format - Format$([DateFromTable],"mmmm yyyy")

[David J.]

In a query, under field, I have the following:

Month: Format$([dateQArelease],"mmmm yyyy")

In this query I also have Analyst and Count: Analyst.

This is an example of a line from the result returned:
Month Analyst Count
December 2006 J. Smith 8
December 2006 R. Jones 7

The purpose of this is to take the various analysts and calculate the total
number of shipments they dealt with during a given month. If I add the
original table cell into the query, but hide it from displaying, it breaks
apart the above example, showing the following:
Month Analyst Count
December 2006 J. Smith 3
December 2006 J. Smith 5
December 2006 R. Jones 2
December 2006 R. Jones 3
December 2006 R. Jones 2

Basically it shows the values for each date. Now, when I go to make a graph
in Access from the query data, it puts all of the values in order of
date...April, May, June, etc. However, if I try and display a regular report,
it organizes them in alphabetical order based on the Month....April, August,
February, etc. I know Access doesn't like the fact there is no day in there,
but how come it can convert properly in the graph, but not in anything else?
Any help would be much appreciated. Thanks.

 

[Allen Browne]

Instead of grouping by:
Format$([DateFromTable],"mmmm yyyy")
try:
DateSerial(Year([DateFromTable]), Month([DateFromTable]),1)

This expression gives you the first of the month as a date. Access can sort
the dates correctly. And you can format the axis of your graph to display
this as mmmm yyyy.