MORE INFORMATION
The
BuildCriteria method returns a parsed criteria string as it would appear in the query design grid or in Filter By Form mode. You may want to set a form's
Filter property based on varying criteria from the user. You can use the
BuildCriteria method to construct the string expression argument for the
Filter property. For example,
strCriteria = BuildCriteria("OrderDate", dbText, "abc")
sets
strCriteria to
Searching for a String Containing a Quotation Mark
To search for a text string containing a quotation mark (") requires special syntax. If you are parsing an expression that contains a quotation
mark, you must use Chr(34) twice to represent that quotation mark. For
example, if you want to search for the string
using the
Find method, parse the expression using the
BuildCriteria method as follows:
strCriteria = BuildCriteria("MyText", dbText, "a" & Chr(34) & Chr(34) &
"bc")
The value of
strCriteria becomes:
You can also use four quotation marks to search for a string containing a
single quotation mark. For example,
strCriteria = BuildCriteria("MyText", dbText, "a""""bc")
also sets
strCriteria to:
NOTE: You can refer to a form control in the expression argument (the third argument) of the
BuildCriteria method. For example:
strCriteria = BuildCriteria("MyText", dbText, Forms!Form1!Text0)
However, when you type the string into the control on the form, you may
want to enclose that string in apostrophes. If this is not possible,
consider searching for quotation marks in the string, and then make the
appropriate modifications to the string before passing it to the
BuildCriteria method.
Searching for Strings Containing Dots (.)
When setting the expression argument (the third argument) of the
BuildCriteria method in code, use the following syntax to delimit the string:
strCriteria = BuildCriteria("x", dbText, """Internet Explorer 5.01""")
The value of
strCriteria becomes:
x = "Internet Explorer 5.01"
If the string is not properly delimited and the character to the right of
the period is numeric, you receive the following error message:
Run-time error '2423':
The expression you entered has an invalid .(dot) or ! operator or
invalid parentheses.
If the character to the right of the dot is a letter, the expression
argument of the
BuildCriteria function is not interpreted correctly. For example,
strCriteria = BuildCriteria("x", dbText, "a.a")
sets
strCriteria to
An error is not generated if the dot is either the first or the last
character in the string; however enclosing the expression argument in three
pairs of quotation marks works, regardless of the position of the dot
in the string.
NOTE: You can refer to a form control in the expression argument of the
BuildCriteria method. For example:
strCriteria = BuildCriteria("MyText", dbText, Forms!Form1!Text0)
However, when you type the string into the control on the form, you may
want to enclose that string in apostrophes. If this is not possible,
consider searching for the dot in the string, and then make the appropriate
modifications to the string before passing it to the
BuildCriteria method.
Using Spaces in the Field Argument
If the field argument (the first argument) of the
BuildCriteria method contains one or more spaces, enclose the argument in square brackets. For example,
strCriteria = BuildCriteria("[a b]", dbText, "x")
sets the value of
strCriteria to:
If you do not use square brackets, a trappable error occurs when you use
the result with other methods or with Access SQL. For example,
strCriteria = BuildCriteria("a b", dbText, "x")
sets
strCriteria to
If you attempt to use
strCriteria with another method or property, you may receive the following error:
Run-time error '3075':
Syntax error (missing operator) in query expression <expression>.