Filter Report by Form - VBA Code

[Mannie G]

I have a report with both text, date and Yes/No fields and I am trying to
filter the report data with a pop-up form. I have fields I want to filter
the data by. I have code that works with 'Text' fields; it is

For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " =
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If

If i take out the '& Chr(34)' it works with the Yes/No fields. The date
field I would like to have as either 'Is Not Null' or 'Is Null' (it is
filter2)

What code do I need to make it work with the three data types? Any help
would be appreciated.

 

[Marshall Barton]

I have a report with both text, date and Yes/No fields and I am trying to
filter the report data with a pop-up form. I have fields I want to filter
the data by. I have code that works with 'Text' fields; it is

For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " =
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If

If i take out the '& Chr(34)' it works with the Yes/No fields. The date
field I would like to have as either 'Is Not Null' or 'Is Null' (it is
filter2)

What code do I need to make it work with the three data types?

Because the syntax is different for the three types, I think
it's easier/clearer to do each field separately:

If Len(Nz(Filter1,"")) > 0 Then 'Text field
strSQL = strSQL & " And [" & Me("Filter" &
intCounter).Tag & "] = " & Chr(34) & Me("Filter" &
intCounter) & Chr(34)
End If

If Not IsNull(Filter2) Then 'Number field
strSQL = strSQL & " And [" & Me("Filter" &
intCounter).Tag & "] = " & Me("Filter" & intCounter)
End If

If Not IsNull(Filter3) Then 'Date/time field
strSQL = strSQL & " And [" & Me("Filter" &
intCounter).Tag & "] = " & Format(Me("Filter" & intCounter),
"\#yyyy-m-d\#")
End If
.. . .
strSQL = Mid(strSQL, 6)