Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date Mismatch in "strwhere" Code 1

Status
Not open for further replies.

lars7

Technical User
Aug 16, 2005
817
GB


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?
 
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
 
Hi PHV,
Your code worked great.

Thanks,

Lars7

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top