ACC: 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 quotes
   strFilter = BuildCriteria("Region", dbText, "'" & strInput & "'")
				

Steps to Reproduce the Behavior

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

1. Press CTRL+G to open the Debug window.
2. In the lower half of the Debug window, type the following:
   
   ?BuildCriteria("Region", dbText, "In")
3. Press ENTER.

You will see one of the described error messages. To see the correct output, type the following in the Debug window

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

and press ENTER. You will see the following output:

   Region = 'In'