Microsoft KB Archive/95978

{|
 * width="100%"|

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

 * }

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(&quot;Description&quot;, &quot;Categories&quot;,_        &quot;[Category ID] = Forms!Form1!Field1&quot;)

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

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

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

  [Category ID] = 'BEVR'

MORE INFORMATION
When you make a function call such as

  DLookUp(&quot;Description&quot;, &quot;Categories&quot;, &quot;[Category ID] = 'BEVR'&quot;)

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(&quot;Description&quot;, &quot;Categories&quot;,_      &quot;[Category ID] = Forms!Form1!Field1&quot;)

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(&quot;Categories&quot;) myvar = &quot;BEVR&quot; S.FindFirst &quot;[Category ID] = '&quot; & myvar & &quot;'&quot;