Display Date With Leading Zeros? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

[Support4John]

SELECT tblArrivals.dteDateArrival AS Arrival, Count(tblArrivals.lngID) AS [Claims] FROM tblArrivals GROUP BY tblArrivals.dteDateArrival UNION SELECT "<All Dates>", 1 FROM tblArrivals UNION SELECT "<New Date>", 1 FROM tblArrivals;

The above SQL works OK on my computer and returns:

<All Dates> 1
<New Date> 1
10/31/2003 40
11/03/2003 62
11/14/2003 43
etc

When I run this on my customers computer with same FE & BE it returns the following:

<All Dates> 1
<New Date> 1
10/31/2003 40
11/14/2003 43
11/3/2003 62
etc

Customer

[HansV]

You probably have a different Short Date format in the Regional Settings control panel.

The obvious suggestion is to use the Format function. But if this related to <post#=315240>post 315240</post#>, that won't help. If the questions are related, it would have been better to ask them in the same thread.

[Support4John]

Hi Hans

Customer

[HansV]

>> Any other suggestions?

For what?

[WendellB]

John
The simplest way to sort dates is to be certain the date is stored as a time/date field. Then sort in your query on the data field, and in another field, return the date in whatever format you wanted.

Also, you didn't answer Hans' question - this sounds very much like the same problem with two slightly different definitions. If it is, this should have been a single thread rather than two.