ACC: Errors Concatenating Variables or Controls (2.0/7.0/97)

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

When you concatenate variables or controls in a function or OpenRecordset method, you may receive one of the following error messages.

In Microsoft Access 97

The Microsoft Jet database engine does not recognize <name> as a valid Field name or expression. (Error 3070)

-or-

Data type mismatch in criteria expression. (Error 3464)

-or-

In Microsoft Access 2.0 and 7.0

Can't bind name '<argument>'

-or-

Type Mismatch

-or-

In Microsoft Access 7.0 and 97

Too few parameters. Expected 1

-or-

In Microsoft Access 2.0

1 parameter expected only 0 supplied.

MORE INFORMATION

These error messages can occur if one of the following is true:

• You incorrectly declare the data type of the argument being passed to the function.
• You include a variable or control name in the function syntax and Microsoft Access is unable to recognize the data type.
• You use incorrect syntax to concatenate the variables.

Microsoft Jet Engine error(3070) or "Can't bind name '<argument>'" Error

You may receive the Microsoft Jet Engine error message or the "Can't bind name '<argument>'" error message when you concatenate a variable or control that has a String data type in a method or function as a Numeric data type. For example, the following sample function produces one of these error messages:

   Call the function as follows: MyFunction ("Seattle")

   Function MyFunction (DataToFind As String)
      Dim MyDB As Database, Myset As Recordset
      Set MyDB = CurrentDB()
      Set Myset = MyDB.OpenRecordset("Employees", DB_OPEN_DYNASET)
      Myset.FindFirst "[City]= " & DataToFind
   End Function

   MySet.FindFirst "[City] = '" & DataToFind & "'"

"Type Mismatch" or "Data Type Mismatch in Criteria Expression" Error

You may receive the "Type Mismatch" or "Data Type Mismatch in Criteria Expression" error message when you concatenate a variable or control that has a Numeric data type in a method or function as a String data type. For example, the following function produces one of these error messages:

   Call the function as follows: MyFunction (3).

   Function MyFunction (NumberToFind As integer)
      Dim MyDB As Database, MySet As Recordset
      Set MyDB = CurrentDB()
      Set MySet = MyDB.OpenRecordset("Order Details", DB_OPEN_DYNASET)
      MySet.FindFirst "[Quantity] = '" & NumberToFind & "'"
   End Function

   MySet.FindFirst "[Quantity] = " & NumberToFind

• When the argument for a user-defined function or method is a String data type, single quotation marks around the string variable are required.
• Dates passed as a string require the number sign (#). For example:

        MySet.FindFirst "[HireDate] = #" & DateToFind & "#"
  

• Numeric data types do not require delimiters. For example:

        MySet.FindFirst "[Quantity] = " & NumericDataToFind
  

"Too few parameters. Expected 1" Error

You may receive this error message when you use the OpenRecordset method in code on an existing query. If the query is a parameter query, you need to explicitly declare the parameter and its data type and set the parameter value for that query in the function.

For example in Query1, which includes fields from the Employees table in the sample database Northwind.mdb (or NWIND.MDB in version 2.0), the following sample code generates the error message when Query1 has the parameter "[Enter a City]" in the criteria for the [City] field:

   Function TestQP()
      Dim MyDB As Database, MySet As Recordset
      Set MyDB = CurrentDB()
      Set MySet = MyDB.OpenRecordset("Query1", DB_OPEN_DYNASET)
      Debug.Print MySet![City]; Tab(10); MySet![Region]
   End Function

   Function TestQP()
      Dim MyDB As Database, MyDef As QueryDef, MySet As Recordset
      Set MyDB = CurrentDB()
      Set MyDef = MyDB.QueryDefs("Query1")
      MyDef![Enter a City] = "Seattle"
      Set MySet = MyDef.OpenRecordset(DB_OPEN_DYNASET)
      Debug.Print MySet![City]; Tab(10); MySet![Region]
      MySet.Close
      MyDef.Close
   End Function

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

   Set MySet = MyDB.OpenRecordset("SELECT * FROM Employees WHERE [City] _
      = '" & Forms!Form1!Field0 & "';")

REFERENCES

For more information about concatenating variables or controls in Microsoft Access versions 1.x, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q96576
   TITLE     : ACC1x: Error Messages when Concatenating Variables or
               Controls