VB Code for form filter with different Data types

[Mannie G]

I am using a pop-up form to allow filtering of data in a report. I have
copied the code from support article 208529 :-

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

I have a problem with the code below because of mixed data types. I have
one text field, one date field and 3 YES/NO checkbox fields. If I use the
code as it is it worls with the text field. if I remove the & Chr(34) it
works OK with the 3 Yes/No fields.

The date field is also causing a problem with or without the Chr(34). In
the Combo box for that field I want to be able to filter the records by 'Is
Not Null' or Is Null' I cant seem to get this to work with or without the
Chr (34) code.

Can anyone suggest a fix to allow the form and code to work with the mixed
data.
Any help would be appreciated.

 

[Marshall Barton]

I am using a pop-up form to allow filtering of data in a report. I have
copied the code from support article 208529 :-

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

I have a problem with the code below because of mixed data types. I have
one text field, one date field and 3 YES/NO checkbox fields. If I use the
code as it is it worls with the text field. if I remove the & Chr(34) it
works OK with the 3 Yes/No fields.

The date field is also causing a problem with or without the Chr(34). In
the Combo box for that field I want to be able to filter the records by 'Is
Not Null' or Is Null' I cant seem to get this to work with or without the
Chr (34) code.

Can anyone suggest a fix to allow the form and code to work with the mixed
data.

I tried to answer this question a couple of days ago in the
reports newsgroup.

 

[Mannie G]

Hi Marshall,

I re-posted the question as I can't find my original question on the reports
newsgroup. I have had no notifications by email and although I have searched
I cannot find the original post. Perhaps you could answer again if it is not
too much trouble.
--
Thanks

Mannie G

I am using a pop-up form to allow filtering of data in a report. I have
copied the code from support article 208529 :-

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

I have a problem with the code below because of mixed data types. I have
one text field, one date field and 3 YES/NO checkbox fields. If I use the
code as it is it worls with the text field. if I remove the & Chr(34) it
works OK with the 3 Yes/No fields.

The date field is also causing a problem with or without the Chr(34). In
the Combo box for that field I want to be able to filter the records by 'Is
Not Null' or Is Null' I cant seem to get this to work with or without the
Chr (34) code.

Can anyone suggest a fix to allow the form and code to work with the mixed
data.

I tried to answer this question a couple of days ago in the
reports newsgroup.

 

[John W. Vinson]

The date field is also causing a problem with or without the Chr(34). In
the Combo box for that field I want to be able to filter the records by 'Is
Not Null' or Is Null' I cant seem to get this to work with or without the
Chr (34) code.

Chr(34) is the doublequote character ", which is an appropriate delimiter for
Text fields (only). Number or Yes/No fields should have no delimiter at all;
Date/Time fields should use # as the delimiter, and the date must be in either
American mm/dd/yyyy format or an unambiguous format such as yyyy.mm.dd.

 

[Marshall Barton]

I re-posted the question as I can't find my original question on the reports
newsgroup. I have had no notifications by email and although I have searched
I cannot find the original post. Perhaps you could answer again if it is not
too much trouble.

Here's what I posted Thursday:

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)

 

[Mannie G]

Great, Thanks for your help
--
Thanks

Mannie G

I re-posted the question as I can't find my original question on the reports
newsgroup. I have had no notifications by email and although I have searched
I cannot find the original post. Perhaps you could answer again if it is not
too much trouble.

Here's what I posted Thursday:

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)

 

[Mannie G]

I was aware of the Chr (34), but not about the # for the date field. Thanks