Sorting by Month and Year Date Only

[GummowN]

I am formatting a date column using format(date,"mmm yyyy&quot
and then sorting on the column.

Unfortunately it is returning April first. I would like January to still appear first.

Any suggestions on this one.

 

[SgtJarrow]

Add a field to your query for the month number....set it to not be shown. Set the field line to:

Format(date, "mm&quot

Sort by this instead of your "displayed" date..... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[mwolf00]

can't you use an order by clause in your query?

SELECT format(s_date,"mmm yyyy&quot
FROM jobs order by cDate(format(s_date,"mmm yyyy&quot) -----------------------------------------------------------------
"The difference between 'involvement' and 'commitment' is like an eggs-and-ham breakfast: the chicken was 'involved' - the pig was 'committed'."
- unknown

mikewolf@tst-us.com

 

[SgtJarrow]

Oops...you want to include year....make that field line read:

Format(date, "mm yyyy&quot

should get you what you want. Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[GummowN]

I will try and explain some more. I have a base query with a field name aliased grouping. From this query a number of consolidated queries are produced. The grouping can changed from Vintage to Customer Age to any other group that is requested. With the vintage it would ne nice to display March 1999 instead of 01/03/1999. When I try this the format statement produces a string (obviously) and therefore sorts alphabetically.

I cant use an additional column because of the way the data is moved through the queries. Any ideas how this is possible.

Additionally I am storing the base query as a sqlstr in a memo field, for each record there is another memo field that contains the sqlstr for the final query - so that sample sizes and groupings can be produced. The "grouping" column must always be called grouping because of the links and its relationship to the report that sits on top.