Need Help With date range search

pds8475

Registered User.
Local time
Today, 09:38
Joined
Apr 26, 2015
Messages
84
Hi
I have a form with a subform which searches the database using multiple criteria using Text Boxes and a search button. One of which is a date range(The code for which was kindly provided by another member) which is entered into 2 text boxes(SDTxt and EDTxt). At first glance the code works. If I enter 03/05/2015 in SDText and say 24/05/2015 in EDTxt and hit the search button(SearchBtn) the correct records are shown in the subform(OfficeSubform). But if I enter 05/06/2015 in SDTxt and 13/06/2015 into EDTxt not only does it show the records between the 2 dates but also all records from May. I cant understand why this would be. So any help would be greatly appreciated.

I have put on a cut down version of the code which just shows the date range search.

Code:
 Private Sub SearchBtn_Click()
  
 Dim strWhere As String
  
  If IsDate(.SDTxt.Value) And IsDate(.EDTxt.Value) Then
            strWhere = strWhere & _
                       "DateBookedIn BETWEEN #" & .SDTxt.Value & "# AND #" & .EDTxt.Value & "#" & AND_STR
                               
        End If
        
        
        ' Clean up and apply/remove filter
        With .OfficeSubform.Form
            If Len(strWhere) <> 0 Then
                strWhere = Left(strWhere, Len(strWhere) - Len(AND_STR))
                .Filter = strWhere
                .FilterOn = True
            Else
                .Filter = vbNullString
                .FilterOn = False
            End If
        End With
    End With
  
End Sub
 
I remember that code ;)

Your SDate is getting interpreted as 6th May because Access uses the American date format. Here's a fix:
Code:
            strWhere = strWhere & _
                       "DateBookedIn BETWEEN " & Format(.SDTxt.Value, "\#mm\/dd\/yyyy\#") & " AND " _
                                               & Format(.EDTxt.Value, "\#mm\/dd\/yyyy\#") & AND_STR
 
Thanks again vbaInet. I should of known it was American date with Microsoft being American.
 

Users who are viewing this thread

Back
Top Bottom