ACC: BuildCriteria Method Returns Error for Certain Expressions

• Microsoft Access versions 7.0, 97

SYMPTOMS

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

When you use the BuildCriteria method, you may receive one of the following errors:

Run-time error '2431': The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier.

-or-

Run-time error '2429': The In operator you entered requires parentheses.

-or-

Run-time error '7956': The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parentheses.

CAUSE

You have used an SQL reserved word for the expression argument of the BuildCriteria method. Specifically, you have used either the word "Select" or "In" for your expression, as in the following example:

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

RESOLUTION

When you use the BuildCriteria method, enclose the expression in single quotation marks, as in the following example:

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

STATUS

This behavior is by design.

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

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.

   Region = 'In' 

REFERENCES

For more information about the BuildCriteria method, search the Help Index for "BuildCriteria Method."

Also, please see the following articles in the Microsoft Knowledge Base:

Q170142 ACC: Issues When Using the BuildCriteria Method

Q178198 ACC97: Run-time error '2448' with BuildCriteria Method

Additional query words: prb filter criteria

Keywords : kbdta AccCon
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbprb