Filtering Number Fields Using Wildcards

[Matthew Pfluger]

I have a form designed to search a query that contains a number field and a
text field. The form contains two text boxes, one for each of the fields in
the query. I want the user to be able to enter any value with an asterisk
("*") for a wildcard. When the user clicks "Search" button, the form runs a
procedure to build a filter string for the form's embedded subform.

However, the subform does not seem to like wildcards on the number field.
For instance, here's a typical filter string:
(qryFindVendor.VendorNumber Like "61251*")

When I apply this filter string using the Form.Filter property, the code
returns the following error:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Despite this error, the subform datasheet usually filters correctly. What
makes this even stranger is that I can apply the same filter through Filter
by Form, and the filter not only works but saves with the form and runs
correctly again upon reopening.

Is there something I'm doing wrong? Thanks.
Matthew Pfluger

 

[Ken Snell MVP]

What you've built as a WHERE string has always worked for me on number
fields without giving any errors. You don't say which version of ACCESS
you're using -- if it's ACCESS 2007, then this behavior may have changed (my
experience primarily is with ACCESS 2002 and 2003 versions).

Try this as the expression, where we cast the number field as a string
value:

(CStr(qryFindVendor.VendorNumber) Like "61251*")