View Single Post
Old March 3rd, 2005, 08:07 PM
DrewMills DrewMills is offline
Registered User
Join Date: Feb 2005
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Trouble with date formats

Here's one people in the States might not have encountered, but it's a curly one for me!

I have a two combo boxes in a form where the user selects a start and end date for a record search. My code retrieves these values, creates an SQL string and changes the underlying QueryDef. The dates are of course dd/mm/yyyy format (Australia).

    If chkDate Then
        dateStart = cmbStartDate.Value
        dateEnd = cmbEndDate.Value
        strSQL = strSQL & " tblLog.[Log Date] Between #" & dateStart & "# And #" & dateEnd & "#"
    End If

My problem is this: when the SQL string is sent to the QueryDef, Access attempts to change the date format to mm/dd/yyyy. This is fine for unambiguous dates, such as 18/2/2005, but where the day is less than 12, it invariably ends up reversed, even if I convert all of my dates to mm/dd/yyyy first. Does anyone have any suggestions?