Prev: Convert to word / excel
Next: Fill out report based on rank from query
From: Mannie G on 8 Apr 2010 13:11
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.

--
Thanks

Mannie G
From: Marshall Barton on 8 Apr 2010 13:48
Mannie G wrote:

>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)

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Convert to word / excel
Next: Fill out report based on rank from query