Access 2000 Sorting by Quarter

[Adam Caramon]

Hi all,

I have a report set up that is derived from a query. In the query I have a DATE by Quarter field, which works just fine. I added criteria of "Between [Enter Begin Quarter] and [Enter End Quarter]"

Once I double click the report, I enter "Q1 2007" "Q4 2008" into the prompts. It correctly pulls data from each quarter, however, it sorts the data by the Q# rather than by the actual date.

Meaning I get:

Q1 2007
blah
blah
blah
Q1 2008
blah
blah
blah
Q2 2007
blah
blah
blah

And I WANT it too look like:

Q1 2007
blah
blah
blah
Q2 2007
etc
Q3 2007
etc
Q4 2007
etc
Q1 2008
etc

Any ideas on how to fix this?

 

[Moniker]

Either sort on a different field, or better, store the quarter info as 2007 Q1, 2007 Q2, etc. You can still display it as Q1 2007, Q2 2007, etc. Just store it as 2007Q1, 2007Q2, etc. and sort on that. For the control, set the display to be:

=Right(YourQuarterField,2) & " " & Left(YourQuarterField,4)

Of course, that will cause Y10K problems, but let's not be absurd.