Order by date query (distinct)

[tcshred]

Hi,

What is the easiest way to create a query to generate a drop down list with dates organised into months? I have tried to do a distinct query then group by, but it turns out this isnt allowed....

I tried:
SELECT DISTINCT Format([cDate],"mmmm yyyy") FROM
AuditTbl ORDER BY Format([cDate],"mmmm yyyy");

Basically I want to get a list which lists all of the months which are in my table for a combo box on a form. This combo box is then used as a criteria for a different query.

I'm sure this must be possible.

 

[JHB]

You are missing the "AS NewFieldName" statement.

SELECT DISTINCT Format([cDate],"mmmm yyyy") AS [NewFieldName] FROM
AuditTbl ORDER BY Format([cDate],"mmmm yyyy");

 

[tcshred]

Thanks. I tried that, but it seems to be ordered alphabetically rather than in order of month?

 

[JHB]

Yes ofcause, the result is alphabetically, so the ordering is alphabetically.

SELECT DISTINCT Format([cDate],"mmmm yyyy") AS Expr1, Format([cDate],"yyyy mm") AS Expr2
FROM AuditTbl ORDER BY Format([cDate],"yyyy mm");

 

[tcshred]

Perfect. Thanks.

 

[JHB]

You're welcome, did you read my signature?