Date order problem (1 Viewer)

[grahamw]

Hi All.
Ive just added some data to a table (in the same format) that contains a number of fields including the date.
I had previously written some code (working) which picks out records in date order. However, now that the data includes the new records it is not picking out dates in perfect order.
The date field is not the primary key. The primary key is a number ref.
Ive tried date sorts but since the date is in UK format dd/mm/yy not
mm/dd/yy (US) the dates are not in order.
Any ideas??
Thanks
Graham, London

 

[KenHigg]

Are you doing anything with the now() function? That can sometime mess things up with date flds...

 

[Summerwind]

Hi Graham

I find dates an absolute nightmare. Whenever I am sorting on or using dates as parameters I now use the DateSerial function. Admitted it is much more work but at least my code works without the constant worry that it will crash on a user at some time in the future.

Syntax: DateSerial(Year(somedate),Month(somedate),Day(somedate)) produces a long integer.

 

[Waabs]

I've made a function that sorts that right out. I can't remember the specifics of it but there is a logical way to swap the months and days that will always get it to the format you want.

 

[Waabs]

Ok it's a code module so you can have your non US dates put into SQL and work as you want:

Dim daypart As Integer
Dim monthpart As Integer
Dim yearpart As Integer

'breaking apart the dates
daypart = DatePart("d", theDate)
monthpart = DatePart("m", theDate)
yearpart = DatePart("yyyy", theDate)

'doing a check for dates like 24/03/2002
If monthpart > daypart Then
theDate = monthpart & "/" & daypart & "/" & yearpart
End If

'doing a check for dates like 11/03/2002
If (daypart > monthpart) And daypart <= 12 Then
theDate = monthpart & "/" & daypart & "/" & yearpart
End If

DateSwap = theDate

 

[grahamw]

Thank you all for your thoughts. I'll let you know how I get on.

 

[modest]

format(DateValue(YourDate), "mm/dd/yyyy")

 

[Bat17]

Waabs
As far as I can see your function will mash dates!
If they are being passed in a consitant fomat there is no need to test values, you just rearange them!
Modest will turn it into the US fromat regardless of local settings.
As far as I can see with your code
12/7/2005 becomes 7/12/2005
13/7/2005 becomes 13/7/2005



Peter

 

[Waabs]

It's quite possible that my code is wrong. It's been a while since I've looked back at the reason I created it the way I did. I think I recall that Access only changed the dates under the circumstances in which I created the code. So it was right for 12/7/2005 to become 7/12/2005 and
13/7/2005 to become 13/7/2005. I will check it out. Again I could be totally wrong!

Edit: I remember now. I am swapping the dates before they're input into the database. Taking them from a text box on a form and then using that to input them into the database. So 12/7/2005 gets swapped to 7/12/2005 so when it is input it can be seen in the table (that has the date format of dd/mm/yyyy) as 12/7/2005 because SQL swaps to US format. 13/7/2005 is slightly different though. It stays that way because SQL doesn't swap it because it knows that the format is dd/mm/yyyy. I am 99% sure that this is the way it works. Feel free to tell me I'm an idiot though.

 

[Waabs]

I just tested it and that is how it works. If you input 13/7/2005 (dd/mm/yyyy) it goes in correctly and appears in the table as 13/7/2005. If you input 12/7/2005 it gets swapped to 7/12/2005.

 

[Pat Hartman]

The bottom line is sort date fields BEFORE you format them. Formatting a date field turns it into a string and regardless of whether your string is mm/dd/yyyy or dd/mm/yyyy, it will NOT sort in proper date order. Text strings sort character by character, left to right. So format your date fields in the form or report control or with the format property in a query rather than the format function.

Dates are stored internally as double precision numbers. So sorting an unformatted date field ALWAYS does a numeric sort and so it ALWAYS produces correct results regardless of which side of the pond you live on.