If Statement with Filter 1

[as0125]

Hello all,

I have a filter correctly working, based on two date fields on a main form, which displays data on a subform. My problem now is that I'd like to include an "If" statement for when the date fields are blank. Here's my code:

Private Sub ApplyDates_Click()

Dim FromFilter
Dim ToFilter

FromDate = Me.FromDate
ToDate = Me.ToDate

If FromDate = Null Then
FromFilter = "#1/1/1900#"
Else
FromFilter = "#" & FromDate & "#"
End If

If ToDate = Null Then
ToFilter = "#12/31/3000#"
Else
ToFilter = "#" & ToDate & "#"
End If

If (Len(FromFilter) & Len(ToFilter)) Then
Me.Subform.Form.Filter = "ReleaseDate>=" & _ FromFilter & "AND ReleaseDate<=" & ToFilter
Me.Subform.Form.FilterOn = True
End If

End Sub

This is the error message I get every time run the filter when leaving the date fields blank:
Syntax error in date in query expression 'ReleaseDate>=## AND ReleaseDate<=##'.

The strange thing is, if I run this without the "If" statement and use either [FromFilter = "#1/1/1900#"] or [FromFilter = "#" & FromDate & "#"] (and the same for ToFilter), the code will run okay for that particular scenario. I only get the error message when having the "If".

Also, if I go thru Debug when the date fields are blank, both FromFilter and ToFilter are equal to "##". I don't understand why they are not being assigned the date values I specified after the "Then" statement.

Hope this makes sense to everyone.
Thanks for any help!

 

[RoyVidar]

Try testing for isnull on the controls:

[tt]if isnull(Me.FromDate) then
FromFilter = "#1/1/1900#"
Else
FromFilter = "#" & Me.FromDate & "#"
end if[/tt]

...

Strongly recommend adding the line:
[tt]Option Explicit[/tt]
as the second line of every module (also Tools | Options - set a checkmark on Require Variable Declaration)

Roy-Vidar

 

[as0125]

Roy-Vidar,

Thanks so much!

 

[assets]

I also find using Public in place of Dim (only wher the filter is used on other forms). I use this to filter records user can see then they login to the database.