Correct date format - dd/mm/yyy or mm/dd/yyyy?

[Sharky II]

Hi

I"m re-using some code i used a long time ago (Access 2000/2003), for a dynamic search. I can search for a date (which in the UK and in my DB is dd/mm/yyyy) by doing:

If InStr(Me![SrchEntryDate], "*") > 0 Then
        where = where & " AND [EntryDate] like '" + Me![EntryDate] + "'"
    Else
       If Not IsNull(Me![SrchEntryDate]) Then
           where = where & " AND [EntryDate] like #" & Format(Me![SrchEntryDate], "mm/dd/yyyy") & "#"
        End If
    End If

And i can search between two date ranges by doing:

If Not IsNull(Me![SrchEntryDateEnd]) Then
    If Not IsNull(Me![SrchEntryDateStart]) Then
        where = where & " AND [EntryDate] between #" + _
        Format(Me![SrchEntryDateStart], "mm/dd/yyyy") + "# AND #" & Format(Me![SrchEntryDateEnd], "mm/dd/yyyy") _
        & "#"
    Else
        where = where
    End If
End If

The only issue is that i can't remember why i used 'mm/dd/yyyy' instead of 'dd/mm/yyyy' - and either/both seem to work just fine?

Does anyone know if it's OK for me to go ahead and change this to dd/mm/yyyy? I can remember that i used mm/dd/yyyy for a reason, but i'm wondering if Access 2007 means that i don't have to do this.

Many thanks!

Eddie

 

[pbaldy]

In VBA code, you need to use US format, mm/dd/yyyy. In other areas you can get away with the other format, but VBA wants it in US format. You might get away with the other format if the date can't be resolved otherwise, like 22/08/2011, but a date like 10/08/2011 will get interpreted as October 8.

 

[Sharky II]

Ah, that was it. Thanks! I will stick to MM/DD/YYYY in my VBA and try to convert it to solve the issue in the other thread you just replied to. Thanks!

Eddie

 

[pbaldy]

No problem Eddie!