Filtering date ranges

[NISMOJim]

Good Morning geniuses,

I'm trying to filter a form using a date range on a field.
txtSLocn is the unbound textbox where the user enters the criteria, Locn is the field from the table/query.
txtSDispStart and txtSDispEnd are the unbound textboxes where the user enters a start and end date, Disp is the date field from the table/query.
Here is the code...

Dim Locn As String
Dim Disp As String




Dim fltLocn As String
Dim fltDisp As String


Me.Filter = ""
Me.Filter = ""
Me.FilterOn = False
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


If IsNull([txtSLocn]) = False And [txtSLocn] <> "" Then
Locn = [txtSLocn]
fltLocn = "Locn like '" & Locn & "*' "
If Me.Filter = "" Then
Me.Filter = fltLocn
Else
Me.Filter = Me.Filter & "and " & fltLocn
End If
End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


If IsNull([txtSDispEnd]) = False Then
Disp = "Disp Between #" & txtSDispStart & "# And #" & txtSDispEnd & "#;"
fltDisp = "Disp Between #" & txtSDispStart & "# And #" & txtSDispEnd & "#;"
If Me.Filter = "" Then
Me.Filter = fltDisp
Else
Me.Filter = Me.Filter & "and " & fltDisp
End If
End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~




If Me.Filter = "" Then
ClearFilter
Else
Me.FilterOn = True
End If


End Sub

When activating the Locn filter, all works great. When I enter dates in the Start and End textboxes, I get a runtime error 3075 syntax error.
What do I need to do with the syntax to filter the Disp field to show records between the two dates?

As always, thank you for your help.

[CJ_London]

you don't normally include the ; in filter strings, try removing it

Also please learn to surround your code with the code tags to preserve formatting/indentation - although long, without indentation it makes it difficult to read

Dim Locn As String
  Dim Disp As String




  Dim fltLocn As String
  Dim fltDisp As String


  Me.Filter = ""
  Me.Filter = ""
  Me.FilterOn = False
 '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


 If IsNull([txtSLocn]) = False And [txtSLocn] <> "" Then
    Locn = [txtSLocn]
    fltLocn = "Locn like '" & Locn & "*' "
    If Me.Filter = "" Then
        Me.Filter = fltLocn
    Else
        Me.Filter = Me.Filter & "and " & fltLocn
    End If
 End If
 '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


 If IsNull([txtSDispEnd]) = False Then
    Disp = "Disp Between #" & txtSDispStart & "# And #" & txtSDispEnd & "#;"
    fltDisp = "Disp Between #" & txtSDispStart & "# And #" & txtSDispEnd & "#;"
    If Me.Filter = "" Then
        Me.Filter = fltDisp
    Else
        Me.Filter = Me.Filter & "and " & fltDisp
    End If
 End If
 '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


 If Me.Filter = "" Then
    ClearFilter
 Else
    Me.FilterOn = True
 End If


 End Sub

[ssanfu]

In addition to Ajax's comments, I would write the code more like Allen Browne's example. (http://allenbrowne.com/ser-62code.html)

This is what I came up with

Private Sub YourButton_Click()
    Dim Locn As String
    Dim Disp As String

    Dim fltLocn As String
    Dim fltDisp As String

    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.

    Me.Filter = ""
    Me.FilterOn = False
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    If IsNull(Me.txtSLocn) = False And Me.txtSLocn <> "" Then
        Locn = Me.txtSLocn
        strWhere = "Locn like '" & Locn & "*' AND "
    End If

    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    'start date
    If Not IsNull(Me.txtSDispStart) Then
        strWhere = strWhere & "[Disp]  >=  #" & Me.txtSDispStart & "# AND "
    End If

    'end date
    If Not IsNull(Me.txtSDispEnd) Then
        strWhere = strWhere & "[Disp] <=  #" & Me.txtSDispEnd & "# AND "
    End If

    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        ClearFilter
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        '   Debug.Print strWhere

        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If

End Sub

[NISMOJim]

Thanks for the help guys, Ajax's answer did the trick. As for the code tag, I see it on here all the time, but not sure how to do it. I'll do more research before posting code next time.
Thanks again!