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-
-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
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.
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
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:
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
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 (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
When you refer to the parameter query, the correct syntax is as follows:
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
The same error message may appear when you concatenate a variable in the
SQL SELECT statement of a OpenRecordset method. A syntactically correct
example is as follows.
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 & "';")
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.
For more information about concatenating variables or controls in Microsoft
Access versions 1.x, please see the following article in the Microsoft
Knowledge Base:
96576Â
(http://kbalertz.com/Feedback.aspx?kbNumber=96576/EN-US/
)
ACC1x: Error Messages when Concatenating Variables or Controls