Filter between dates

[Guest]

I have a form that I want to show only the date that are in FilterDateStart
and FilterDateEnd. I have the code below, but I am getting the "error 13:
data mismatch". What am I doing wrong?

-----start code---------
Dim strFilter As String
Const conJetDate = "\#mm\/dd\/yyyy\#"

If IsDate(Me.FilterDateStart) Then
strFilter = "DueDate > " & Format(Me.FilterDateStart + 1, conJetDate) &
"" _
And "DueDate <= " & Format(Me.FilterDateEnd, conJetDate) & ")"
Me.Filter = strFilter
Me.FilterOn = True
End If
------end code-----------

 

[Douglas J. Steele]

Your And is outside of the quotes, plus you've got an unnecessary "" there
(which adds a single double-quote to the string) and an unmatched ")" at the
end.

If IsDate(Me.FilterDateStart) Then
strFilter = "DueDate > " & Format(Me.FilterDateStart + 1, conJetDate) &
_
" And DueDate <= " & Format(Me.FilterDateEnd, conJetDate)
Me.Filter = strFilter
Me.FilterOn = True
End If

Also, what if FilterDateEnd isn't a date?

If IsDate(Me.FilterDateStart) Then
strFilter = "DueDate > " & Format(Me.FilterDateStart + 1, conJetDate)
If IsDate(Me.FilterDateEnd) Then
strFilter = strFilter & " And DueDate <= " & _
Format(Me.FilterDateEnd, conJetDate)
End If
Me.Filter = strFilter
Me.FilterOn = True
End If

 

[Guest]

Thank you for your help! It works.

Your And is outside of the quotes, plus you've got an unnecessary "" there
(which adds a single double-quote to the string) and an unmatched ")" at the
end.

If IsDate(Me.FilterDateStart) Then
strFilter = "DueDate > " & Format(Me.FilterDateStart + 1, conJetDate) &
_
" And DueDate <= " & Format(Me.FilterDateEnd, conJetDate)
Me.Filter = strFilter
Me.FilterOn = True
End If

Also, what if FilterDateEnd isn't a date?

If IsDate(Me.FilterDateStart) Then
strFilter = "DueDate > " & Format(Me.FilterDateStart + 1, conJetDate)
If IsDate(Me.FilterDateEnd) Then
strFilter = strFilter & " And DueDate <= " & _
Format(Me.FilterDateEnd, conJetDate)
End If
Me.Filter = strFilter
Me.FilterOn = True
End If