ACC: Issues When Using the BuildCriteria Method

ID: Q170142

The information in this article applies to:

Microsoft Access versions 70, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills

In certain situations, the BuildCriteria method does not work as expected or 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. For example, 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")

will set strCriteria to


   OrderDate = "abc"

Searching for a String Containing a Quotation Mark

Searching 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")

will also set 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 enter 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, """Access 2.0""")

The value of strCriteria becomes:


   x = "Access 2.0"

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 will not be interpreted correctly. For example,


   strCriteria = BuildCriteria("x", dbText, "a.a")

will set strCriteria to


   x = [a].[a]

An error will not be 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 will work, 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 enter 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")

The value of strCriteria becomes:


   [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")

will set 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 <expression>.

REFERENCES

For more information about using quotation marks in strings, search the Help Index for "quotation marks in expressions," and then select "Quotation Marks in Strings."

Additional query words: period periods


Keywords          : 
Version           : 7.0 97
Platform          : 
Issue type        :

Last Reviewed: April 26, 1999