ACC2000: BuildCriteria Method Returns Error for Certain Expressions

ID: Q197586

The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills.


SYMPTOMS

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.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp


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" & "'")


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:


    3. 
?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:
region = 'in'


REFERENCES

For more information about the BuildCriteria method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "BuildCriteria method" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: prb filter criteria 


Keywords          : kbdta PgmErr 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: July 15, 1999