Ordering a DISTINCT query of dates?

[sponge]

Is this possible, if using the following:

SELECT DISTINCT (Format([Month],"mmm yyyy")) FROM Table;

Adding a ORDER BY [Month]; gives a "distinct and order by" conflict...

Is there any way to alleviate this?

I want to display a "mmm yyyy" formatted combobox in order (i.e. Jan 2005, Feb 2005, Mar 2005, etc...)

TIA!

 

[RuralGuy]

Have you tried running your DISTINCT Query through an OrderBy Query?

 

[sponge]

Hi,

Sorry.. I'm not sure what you mean..

It orders alphabetically when I use the "mmm yyyy" format.

What I'd like to do is get the combo box to order by date. I'm using a SQL statement in the Row Source property of the combo box.

When I try to use a "SORT BY" at the end of the current SQL statement, I get an error...

If I only use a SORT BY without DISTINCT, it orders correctly but of course I get recurring "Jan 2005", "Feb 2005", etc. values in the combo box since it's no longer querying for distinct values only.

 

[RuralGuy]

Create your DISTINCT Query and save it. Then use *that* query as the source of a Sort-by query! If that works as you desire then set the Row Source of your ComboBox to the second query.

Be sure and leave a regular date field in your query or you can't sort it by date.

 

[yhgtbfk]

I am not sure if this helps, but I think you can only do an ORDER BY on fields you have selected.

I cant really remember, but use the key word AS. Something like:

SELECT DISTINCT (Format([Month],"mmm yyyy") AS month) FROM Table ORDER BY month;

Soory I cant be more helpful

 

[WayneRyan]

Sponge,

That's why it's two queries; the first to get the distinct values; and preserve
the month (for sorting).

Then the second to give you the results.

Wayne

 

[Pat Hartman]

When you format a date you turn it into a text string. Text strings are sorted character by character - left to right so Aug comes before Jan. If you want to order by a date field, you MUST order by the unformatted value or format the value into logical order - yyyy/mm/dd and order by that.

So, order by the unformatted field. I don't have time to play with this but if the Distinct predicate requires the order by column to be selected, select the unformatted date field and then select the date field and format it so the field appears twice. You can hide the first column in your combo.

 

[sponge]

Many thanks for all of your input. You've all been of great help.

Wayne - the SQL statements you posted in the other topic worked great. Thanks!