ACC2000: BuildCriteria Method Returns Error for Certain Expressions

BuildCriteria("FieldName", dbText, "IN")
				

BuildCriteria("FieldName", dbText, "'" & "IN" & "'")
				

MORE INFORMATION

Both "In" and "Select" are SQL Reserved Words that can be used to indicate the presence of a subquery within a standard SQL statement. When these words are supplied as the expression argument for the BuildCriteria method, Microsoft Access expects a subquery to follow.

Enclosing the expression argument of the BuildCriteria method in single quotation marks will not affect the behavior of the method for text type arguments. The following code fragment gives an example of how this could be applied within Visual Basic for Applications code:

Dim strMsg As String
Dim strInput As String
Dim strFilter As String

strMsg = "Please enter your text."
' Prompt user for input.
strInput = InputBox(strMsg)
' Build criteria string and assign it to a filter string.
' Enclose the user input within single quotation marks
strFilter = BuildCriteria("Region", dbText, "'" & strInput & "'")
				

Steps to Reproduce Behavior

You can easily reproduce the described errors in the Immediate window, as follows:

1. Press CTRL+G to open the Immediate window.
2. Type the following in the Immediate window:

   ?BuildCriteria("Region", dbText, "In")
   					

3. Press ENTER.

Note that you receive one of the error messages mentioned in the "Symptoms" section. To see the correct output, type the following in the Immediate window

?BuildCriteria("Region", dbText, "'" & "In" & "'")
				

and press ENTER. Note that you receive the following output: