Date Mismatch in "strwhere" Code 1

[lars7]

Hi I’m creating a query with code and attaching a where statement (strwhere2) to get information from 2 text boxes into the criteria of the field [Date Received].

strSQL = "SELECT CoverTable.Ref, LocumRequestInformation.Contact, CoverTable.Available, LocumRequestInformation.Hospital, LocumRequestInformation.Department, LocumRequestInformation.Directorate, CoverTable.Date, CoverTable.Date2, CoverTable.Time " & _
"FROM LocumRequestInformation INNER JOIN CoverTable ON LocumRequestInformation.Ref = CoverTable.Ref " & _
"Where (((LocumRequestInformation.Ref) Is Not Null)) " & strwhere & _
"ORDER BY LocumRequestInformation.Directorate DESC"


If Trim(Me!List1 & "") <> "" Then
strwhere2 = strwhere2 & "AND Directorate='" & Me!List1 & "' "
End If
If Trim(Me!Text1 & "") <> "" Then
strwhere2 = strwhere2 & "AND [Date Recieved]>=#" & Me![Text1] & "#"
End If
If Trim(Me!Text2 & "") <> "" Then
strwhere2 = strwhere2 & "AND [Date Recieved]<=#" & Me![Text2] + 1 & "#"
End If


This should result in me getting between dates in my query criteria but for some reason the dates can be sent to the query in the wrong order

Example:

Text1 = 01/01/2011
Text2 = 01/05/2011

Result in the criteria of field [date received] = >=#01/01/2011# And <=#05/02/2011#

Can anyone point me in the right direction to solve this?

 

[PHV]

Well, I'd use non ambiguous date format, like this:
If Trim(Me!Text1 & "") <> "" Then
strwhere2 = strwhere2 & "AND [Date Recieved]>=#" & Format(Me!Text1, "yyyy-mm-dd") & "#"
End If
If Trim(Me!Text2 & "") <> "" Then
strwhere2 = strwhere2 & " AND [Date Recieved]<=#" & Format(Me!Text2 + 1, "yyyy-mm-dd") & "#"
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lars7]

Hi PHV,
Your code worked great.

Thanks,

Lars7