Microsoft KB Archive/210610

= ACC2000: Issues That Arise When Using the BuildCriteria Method =

Article ID: 210610

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210610



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
In certain situations, the BuildCriteria method does not work as expected, or it returns values that cannot be used by other methods or SQL statements.

This article addresses the following topics:
 * Searching for a string containing a quotation mark (")
 * Searching for a string containing a dot (.)
 * Using spaces in the Field argument



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

OrderDate = "abc"

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

a"bc

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:

MyText = "a""bc"

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:

MyText = "a""bc"

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

x = [a].[a]

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:

[a b] = "x"

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

a b = "x"

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.

