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

Last reviewed: June 8, 1997
Article ID: Q95978

The information in this article applies to:

Microsoft Access versions 1.0, 1.1

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 PgmOthr SynGnrl
Version : 1.0 1.1
Platform : WINDOWS
Hardware : X86
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 8, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.