Searching (i.e., 3,000,000) in a number field using Filter-By-Form

[Guest]

Syntax error.

I created a form from a table that contains numerous number fields. The
number fields are formated as "standard" and data is displayed like (i.e.,
3,000,000) but if I try to use the Filter-By-Form and enter 3,000,000 I get a
syntax error.

If I enter3000000 I have no problem. It is very difficult to consistently
correctly enter data without the help of commas. Is there a workaround for
this type of issue.

Thanks

David L.

 

[Dirk Goldgar]

Syntax error.

I created a form from a table that contains numerous number fields.
The number fields are formated as "standard" and data is displayed
like (i.e., 3,000,000) but if I try to use the Filter-By-Form and
enter 3,000,000 I get a syntax error.

If I enter3000000 I have no problem. It is very difficult to
consistently correctly enter data without the help of commas. Is
there a workaround for this type of issue.

I'm not surprised you're getting the error with Filter By Form, because
it's just not that bright. As far as it's concerned, a comma is part of
a text string, not a numeric value. Note that you *can* use the commas
when entering data in the form itself; it's only a problem with Filter
By Form and entering a value on the Criteria line of a query, because
these both use the built-in BuildCriteria method to interpret your entry
with respect to the type of field you're applying criteria to.

If you need to enter the commas for filtering, the only workaround I can
suggest is to build your own (unbound) filter form, with text boxes for
the various fields you might want to filter by. If you apply a numeric
format to one of those text boxes, the value of the text box will be the
proper number for use in build a filter string to apply to your original
form.