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?

Drew

I am not sure why you need to force the ## date issue in the SQL string. Try this for grins:

'==========
strSQL = strSQL & " tblLog.[Log Date] Between " & "'" & dateStart & "'" & " And " & "'" & dateEnd & "'"
'==========

Access should just take the value without forcing the date issue.

HTH
:D

mmcdonal

Hmm, well I just end up with a run-time error saying I cancelled the previous operation. This relates to a "data type mismatch" in the criteria of the query. Doesn't seem to like the 'dd/mm/yyyy' format. I tried enabling ANSI92 syntax, but it didn't help. looks like I might just have to rearrange the dates using string handlers:-(

Good Ole US Dates. Don't you just love them!

 Try using
strSQL = strSQL & " tblLog.[Log Date] Between #" & Format(dateStart,"mm/dd/yy") & "# And #" & Format(dateEnd,"mm/dd/yy") & "#"

Unless you are using an SQL Server, this usually works, but only if dateStart and dateEnd are Dim'd as dates.

Good luck

ElanSolutionsLtd