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?