Arthur Fuller
artful at rogers.com
Sat Apr 10 19:52:31 CDT 2004
The bug is that Format() creates a string not a date field. To get accurate date order, include the date field as well but don't show it, just order by it. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: Friday, April 02, 2004 5:57 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] ORDER BY BUG using Date format AARHG Hi Sander Well - but what is the bug and how did you solve it? /gustav > Hi gustav, > I've solved the problem. Twas a nasty one. > but euh try it if you want. create a table with a date field like > below and run the query...no go! > Thanx anyway. > Sander > Gustav Brock <gustav at cactus.dk> wrote: > Hi Sander > Your "stupid" test is correct as it sorts DESC on a string. > Try this which I think is what Bert-Jan has in mind: > SELECT DISTINCT > Format([TblArbo].[Datum], "dd-mmm-yyyy") > FROM > TblArbo > ORDER BY > [TblArbo].[Datum] DESC; > /gustav >> I've got a combobox based on a query: >> SELECT DISTINCT [TblArbo].[Datum] >> FROM TblArbo >> ORDER BY [TblArbo].[Datum] DESC; >> Resulted in: >> 01-10-2003 >> 02-09-2003 >> 21-08-2001 >> The user asked if this could be printed in the following format: >> dd-mmm-yyyy No problem, says stupid me: SELECT DISTINCT >> Format([TblArbo].[Datum],"dd-mmm-yyyy") >> FROM TblArbo >> ORDER BY Format([TblArbo].[Datum],"dd-mmm-yyyy") DESC; >> But now the ORDER BY ONLY checks the DD part?!?!?!?! >> So this results in: >> 21-08-2001 >> 02-09-2003 >> 01-10-2003 >> ?!!??! What kind of nonsense is this? -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com