ACC2000: Errors Concatenating Variables or Controls

ID: Q210244

The information in this article applies to:

Microsoft Access 2000

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

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

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

Run-time error '3070':
The Microsoft Jet database engine does not recognize '<name>' as a valid field name or expression.

-or-

Run-time error '3464':
Data type mismatch in criteria expression.

-or-

Run-time error '3061':
Too few parameters. Expected 1.

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.

This article presents examples of how these errors can occur and explains how to correct them.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

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

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:


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

To call this function type the following in the Immediate window, and then press ENTER:


MyFunction("Seattle")

The correct syntax for the last line of the code above is as follows:


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:


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

To call this function, type the following in the Immediate window, and then press ENTER:


MyFunction(3)

The correct syntax for the next to the last line of the code above is as follows:


MySet.FindFirst "[Quantity] = " & NumberToFind

Keep the following requirements in mind:

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, 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 DAO.Database, MySet As DAO.Recordset
   Set MyDB = CurrentDB()
   Set MySet = MyDB.OpenRecordset("Query1", dbOpenDynaset)
   Debug.Print MySet![City]; Tab(10); MySet![Region]
End Function

When you refer to the parameter query, the correct syntax is as follows:


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

The same error message may appear when you concatenate a variable in the SQL SELECT statement of an OpenRecordset method. A syntactically correct example is as follows:


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

This SELECT statement points to a control on a form for the WHERE clause. [City] is a Text field type and the contents of the control are Text.

Additional query words: inf parameters

Keywords : kberrmsg kbprg kbdta AccCon
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto