Sorting formatted dates

[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

 

[Cheryl Fischer]

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.

 

[Katherine]

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!

 

[John Spencer (MVP)]

ONE method

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

 

[Katherine]

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

.