ACC1x: Criteria Parameters Require Concatenated References (1.x)

ID: Q95978

The information in this article applies to:

SUMMARY

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

Microsoft Access has both a SQL interpreter and an Access Basic interpreter. Because some Access Basic commands and functions require SQL syntax as a parameter, it may become confusing how the two work together.

For example, if you need to look up a description in a table called Categories where [Category ID] is equal to whatever value is currently in Forms!Form1!Field1, you cannot use the following code.

NOTE: In the following sample code, an underscore _ is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code. 


   DLookUp("Description", "Categories",_
        "[Category ID] = Forms!Form1!Field1")


Instead, you must concatenate the value of Forms!Form1!Field1 into the criteria parameter as shown below: 


   DLookUp("Description", "Categories",_
      "[Category ID] = '" & Forms!Form1!Field1 & "'")


This means that if the value of Forms!Form1!Field1 is "BEVR," the criteria parameter in the expression above would evaluate to: 


   [Category ID] = 'BEVR'


MORE INFORMATION

When you make a function call such as 


   DLookUp("Description", "Categories", "[Category ID] = 'BEVR'")


Access Basic internally creates a SQL statement with the parameters you supply. In this case, the resulting SQL statement is: 


   SELECT Description FROM Categories WHERE [Category ID] = 'BEVR'


Access Basic sends this statement to the Access SQL engine, which returns the result of the SELECT statement back to Access Basic, and, in turn, back to your DLookUp() expression.

Note that a DLookUp() coded as follows 


   DLookUp("Description", "Categories",_
      "[Category ID] = Forms!Form1!Field1")


would result in a WHERE clause that looks like: 


   [Category ID] = Forms!Form1!Field1


The SQL parser is designed to accept SQL syntax, and the Forms!Form1!Field1 reference is Access Basic syntax, so the form reference cannot be expected to work. Instead, the resulting evaluated criteria parameter must be done in such a way as to accommodate the syntax rules of the SQL parser.

Note that this also applies to other Access Basic methods and functions that require a criteria parameter. For example, Find methods use a criteria parameter as shown below: 


   Dim D As Database, S As Snapshot
   Set D = CurrentDB()
   Set S = D.CreateSnapshot("Categories")
   myvar = "BEVR"
   S.FindFirst "[Category ID] = '" & myvar & "'"


REFERENCES

Microsoft Access "User's Guide", Version 1.0, page 181 and Appendix C

Additional query words: concatenation 


Keywords          : kbprg 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: March 18, 1999