ACC2000: Issues That Arise When Using the BuildCriteria Method

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

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, 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: The value of strCriteria becomes: You can also use four quotation marks to search for a string containing a single quotation mark. For example, 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: 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: The value of strCriteria becomes: If the string is not properly delimited and the character to the right of the period is numeric, you receive the following error message: 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, 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: 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, 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, sets strCriteria to If you attempt to use strCriteria with another method or property, you may receive the following error:

For more information about using quotation marks in strings, click Microsoft Access Help on the Help menu, type quotation marks in strings in the Office Assistant or the Answer Wizard, and then click Search to view the topic.