Improper coding for my Form Filter

[shhsecurity]

I found some code that lets me Filter a Report from a Popup Form. It uses 6
combo boxes with each box filtering my VehicleRecords table to the record I
am looking for. The code for this can be found here:
http://support.microsoft.com/kb/q208529
I modified just a few lines so that it would point to my VehicleRecords table.

When I enter information into any one of the combo boxes I get a Run Time
Error 13 - Type Mismatch, and then I am pointed to this code.

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 6))

' Set the Filter property.
Reports![VehicleREG].Filter = strSQL
Reports![VehicleREG].FilterOn = True
End If
End Sub

This part of the code is highlighted in yellow. What is wrong here?

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""

 

[John Smith]

You cannot use the And operator in this context. I think that your line
should be:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] = " _
& Chr(34) & Me("Filter" & intCounter) & Chr(34)

HTH
John

 

[Marshall Barton]

I found some code that lets me Filter a Report from a Popup Form. It uses 6
combo boxes with each box filtering my VehicleRecords table to the record I
am looking for. The code for this can be found here:
http://support.microsoft.com/kb/q208529
I modified just a few lines so that it would point to my VehicleRecords table.

When I enter information into any one of the combo boxes I get a Run Time
Error 13 - Type Mismatch, and then I am pointed to this code.

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 6))

' Set the Filter property.
Reports![VehicleREG].Filter = strSQL
Reports![VehicleREG].FilterOn = True
End If
End Sub

This part of the code is highlighted in yellow. What is wrong here?

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""

The quotes around the And are out of whack. Try this:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag _
& "] = " & Chr(34) & Me("Filter" & intCounter) _
& Chr(34) & " And "

The Len(...) - 6 should be Len(...) - 5

Your code assumes that all of the fields you're filtering on
are Text fields. It will not work for a numeric type field.

 

[shhsecurity]

I found some code that lets me Filter a Report from a Popup Form. It uses 6
combo boxes with each box filtering my VehicleRecords table to the record I
am looking for. The code for this can be found here:
http://support.microsoft.com/kb/q208529
I modified just a few lines so that it would point to my VehicleRecords table.

When I enter information into any one of the combo boxes I get a Run Time
Error 13 - Type Mismatch, and then I am pointed to this code.

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 6))

' Set the Filter property.
Reports![VehicleREG].Filter = strSQL
Reports![VehicleREG].FilterOn = True
End If
End Sub

This part of the code is highlighted in yellow. What is wrong here?

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""

The quotes around the And are out of whack. Try this:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag _
& "] = " & Chr(34) & Me("Filter" & intCounter) _
& Chr(34) & " And "

The Len(...) - 6 should be Len(...) - 5

Your code assumes that all of the fields you're filtering on
are Text fields. It will not work for a numeric type field.

The Len(...) - 6 should be Len(...) - 5 ? Does this code have anything to
do with the amount of comboboxes that I have? I have 6 of them.

I changed this code to this:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "

and it works, except that now I am getting a syntax error that pops up for
any of the combo boxes. "Syntax error in string in query expression"
'([AutoModelFilter] = "RELIANT)'
AutoModelFilter is the properties tag for one of the comboboxes.

And where/what do I add to make this form filter seacrh in numerical also so
that I can look up license numbers?

 

[Marshall Barton]

I found some code that lets me Filter a Report from a Popup Form. It uses 6
combo boxes with each box filtering my VehicleRecords table to the record I
am looking for. The code for this can be found here:
http://support.microsoft.com/kb/q208529
I modified just a few lines so that it would point to my VehicleRecords table.

When I enter information into any one of the combo boxes I get a Run Time
Error 13 - Type Mismatch, and then I am pointed to this code.

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 6))

' Set the Filter property.
Reports![VehicleREG].Filter = strSQL
Reports![VehicleREG].FilterOn = True
End If
End Sub

This part of the code is highlighted in yellow. What is wrong here?

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""

The quotes around the And are out of whack. Try this:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag _
& "] = " & Chr(34) & Me("Filter" & intCounter) _
& Chr(34) & " And "

The Len(...) - 6 should be Len(...) - 5

Your code assumes that all of the fields you're filtering on
are Text fields. It will not work for a numeric type field.

The Len(...) - 6 should be Len(...) - 5 ? Does this code have anything to
do with the amount of comboboxes that I have? I have 6 of them.

I changed this code to this:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "

and it works, except that now I am getting a syntax error that pops up for
any of the combo boxes. "Syntax error in string in query expression"
'([AutoModelFilter] = "RELIANT)'
AutoModelFilter is the properties tag for one of the comboboxes.

And where/what do I add to make this form filter seacrh in numerical also so
that I can look up license numbers?

The - 5 in Len(...) - 5 is the number of characters in
" And " It has nothing to do with the number of combo
boxes.

The code you posted above looks good. The missing quote
after RELIANT is being thrown away by the - 6 in
Len(...) - 6 along with the trailing And. I told you to
change the 6 to 5.

The error message also includes ( and ), which the posted
code does not include. I have no idea where those came
from, but, in this case, they don't matter.

 

[shhsecurity]

I changed the 6 back to a 5 and now the form filter works... but doesn't
work. What I mean is, when I select or enter a query into any one of the
combo boxes then click on Set Filter a small window pops up and asks me to:
Enter Parameter Value. I enter the same query I clicked or typed in again
and It will not filter. I still get all records showing.

The original code once again is here. http://support.microsoft.com/kb/q208529
Maybe the code is just bad to begin with?

shhsecurity wrote:
I found some code that lets me Filter a Report from a Popup Form. It uses 6
combo boxes with each box filtering my VehicleRecords table to the record I
am looking for. The code for this can be found here:
http://support.microsoft.com/kb/q208529
I modified just a few lines so that it would point to my VehicleRecords table.

When I enter information into any one of the combo boxes I get a Run Time
Error 13 - Type Mismatch, and then I am pointed to this code.

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 6))

' Set the Filter property.
Reports![VehicleREG].Filter = strSQL
Reports![VehicleREG].FilterOn = True
End If
End Sub

This part of the code is highlighted in yellow. What is wrong here?

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""

The quotes around the And are out of whack. Try this:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag _
& "] = " & Chr(34) & Me("Filter" & intCounter) _
& Chr(34) & " And "

The Len(...) - 6 should be Len(...) - 5

Your code assumes that all of the fields you're filtering on
are Text fields. It will not work for a numeric type field.

The Len(...) - 6 should be Len(...) - 5 ? Does this code have anything to
do with the amount of comboboxes that I have? I have 6 of them.

I changed this code to this:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "

and it works, except that now I am getting a syntax error that pops up for
any of the combo boxes. "Syntax error in string in query expression"
'([AutoModelFilter] = "RELIANT)'
AutoModelFilter is the properties tag for one of the comboboxes.

And where/what do I add to make this form filter seacrh in numerical also so
that I can look up license numbers?

The - 5 in Len(...) - 5 is the number of characters in
" And " It has nothing to do with the number of combo
boxes.

The code you posted above looks good. The missing quote
after RELIANT is being thrown away by the - 6 in
Len(...) - 6 along with the trailing And. I told you to
change the 6 to 5.

The error message also includes ( and ), which the posted
code does not include. I have no idea where those came
from, but, in this case, they don't matter.

 

[Marshall Barton]

I changed the 6 back to a 5 and now the form filter works... but doesn't
work. What I mean is, when I select or enter a query into any one of the
combo boxes then click on Set Filter a small window pops up and asks me to:
Enter Parameter Value. I enter the same query I clicked or typed in again
and It will not filter. I still get all records showing.

The original code once again is here. http://support.microsoft.com/kb/q208529
Maybe the code is just bad to begin with?

The code in the article is fine, as far as it goes. As I
said before, it makes no attempt to deal with filtering
numeric or date field types. At this time, do not attempt
to use a filter for anything other than a Text type field.
If you did, it should generate a type mismatch error.

I would not try to provide users with this kind of after the
fact filtering feature so I have no personal experience with
this specific technique, but that code is straightforward
enough.

If you're being prompted for something, it means that the
name you're being promted for is not a field in the
table/query or it's an unquoted string. Check the prompt
string in the pop up and try to figure out where it's coming
from. Often, it means that you spelled a field name
incorrectly (in each combo box's Tag property).

 

[shhsecurity]

Thank you! You were absolutely right. The tags were all mispelled. The
frmFilter works wonderfully now. As long as I create tables with text fields
for my numerical entries I have absolutely no problems. This little filter
for the 5000+ records I have stored works faster and looks more business like
than going thru a query and I appreciate all of your time you spent on
helping me. Thank you again Marshall.

 

[Marshall Barton]

Great, first hurdle overcome.

Now, you should fix the search code to accomodate the
numeric and date type fields. Do not pervert your table
design by using Text fields for everything, which will cause
more problems than it solves.

The thing you need to do is add more code that applies the
appropriate delimiter according to the field type. One
quick and dirty approach would be to add a type indicator
character (T,N,or D) to the front of the field name in the
Tag property. This is a rather shabby approach because a
change to you table may also require a change to this form,
which is not good. A better way would be to refer to the
TableDef of the table that contains the field so you can
determine the field type directly from its definition.

Once you determine the general type of the field, then the
code can check it and supply the proper delimiters:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb()
Set tdf = db.TableDefs!nameoftable
. . .
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag _
& "] = "
Select Case tdf.Fields(Me("Filter" & intCounter).Tag).Type
Case dbText, fbMemo 'use quotes
strSQL = strSQL & Chr(34) & Me("Filter" & intCounter) _
& Chr(34) & " And "
Case dbDate 'use # signs
strSQL = strSQL & Format(Me("Filter" & intCounter), _
"\#m\/d\/yyyy\#") & " And "
Case Else ' numeric - no delimiters
strSQL = strSQL & Me("Filter" & intCounter) & " And "
End Select
. . .