Sorting by date

[jnix]

I have a field which shows year and month, such as 20017. When I try to sort by this field, 200111 sorts before 20017. It sorts okay in a query but not on the report. How can I make this work?
Thanks,
jnix

 

[mikey69]

Somewhere along the way you have set the field as a text field, I think. You could get over the problem by referring to Jan as "01", Feb as "02" and so on.

 

[Steve101]

Mikey,
SOunds like you might be using Sorting and Grouping fields within your report. Be aware if you are, that the Sorting and Grouping sort order takes presidence of any ORDER BY provided in the report's recordsource, and this is probably what is causing the sorting to appear as not working within your report.

The solution is to control the sorting from within the Sorting and Grouping fields (ie. by adding another field here, in the right position) OR removing sorting and grouping from the report, and thus letting the report's underlying recordsource query control the order.

Hope this helps. Best regards to Table Mountain,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[mikey69]

Steve - not my original problem. I just commented. But your comment is a good one. As with so many of these posts, there are lotsdo f little implications

Table Mountain misses you, by the way!

 

[Steve101]

Yes, my response should have been addressed to jnix who started the post.

Cheers, Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[danvlas]

jnicks:
You can get rid of the problem if you use the leading zero of month in building the year + month.
For this, you could use Year(YourField) & Format(Month(YourField),"00&quot

And if you want the field to be numeric, use:
Year(YourField) *100 + Month(YourField)

Regards,

Dan