[AccessD] ORDER BY BUG using Date format AARHG

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




More information about the AccessD mailing list