Sorting formatted dates

  • Thread starter Thread starter Katherine
  • Start date Start date
K

Katherine

Hi!

I'm working on a query that I'll use as the record
source for a list box on a printing dialog form. I know
this is probably easy, but I can't get the query to sort
quite right; here it is:

SELECT DISTINCT Format([EventDate],"mmmm yyyy") AS
MonthYear FROM Events;

I want to sort the results in descending order, but
the sort ends up alphabetical instead of by date.

Can someone help me with this?

Thank you in advance.

Kath
 
You could add another column to your query design and insert EventDate into
it. Select Descending in the Sort row, remove the sort from your
"Month/Year" column and then un-check the Show checkbox.
 
Thanks for the suggestion...I added the EventDate
field to the query, set the sort order, then deselected
Show. I then got this error message:

OrderBy clause conflicts with distinct

Since this query is for a dialog form, I really only
wanted to show any month one time only in the list box (I
have more than one Event in each month, which is why I
wanted the Distinct feature.)

Any other suggestions?

Thanks again!
 
ONE method

SELECT DISTINCT Format([EventDate],"mmmm yyyy") AS
MonthYear FROM Events
ORDER BY Format([EventDate], "yyyymm")
 
Thank you! That worked!

-----Original Message-----
ONE method

SELECT DISTINCT Format([EventDate],"mmmm yyyy") AS
MonthYear FROM Events
ORDER BY Format([EventDate], "yyyymm")
Hi!

I'm working on a query that I'll use as the record
source for a list box on a printing dialog form. I know
this is probably easy, but I can't get the query to sort
quite right; here it is:

SELECT DISTINCT Format([EventDate],"mmmm yyyy") AS
MonthYear FROM Events;

I want to sort the results in descending order, but
the sort ends up alphabetical instead of by date.

Can someone help me with this?

Thank you in advance.

Kath
Click to expand...
.
Click to expand...
 
Back
Top