ACC1x: Error Messages When Concatenating Variables or Controls

ID: Q96576

The information in this article applies to:

Microsoft Access versions 1.0, 1.1

SUMMARY

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

When you are concatenating variables or controls in a function or CreateDynaset method, you may receive one of the following error messages:

Can't bind name '<argument>'


   -or-

Type Mismatch


   -or-

1 parameter expected only 0 supplied

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

You have incorrectly declared the data type of the argument being passed to the function.

You are including a variable or control name in the function syntax and Microsoft Access is unable to recognize the data type.

You are using incorrect syntax to concatenate the variables.

This article presents examples in which these errors occur and explains how to correct them.

MORE INFORMATION

"Can't bind name '<argument>'Error message

You receive the "Can't bind name" 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 function produces the "Can't bind name 'Davolio'" error message:


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

   Function MyFunction (DataToFind As String)
      Dim MyDB As Database, myset As Dynaset
      Set MyDB = CurrentDB()
      Set myset = MyDB.CreateDynaset("Employees")
      Myset.FindFirst "[Last Name]= " & DataToFind
   End Function

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


   MySet.FindFirst "[Last Name] = '" & DataToFind & "'"

"Type Mismatch" Error Message

You receive the "Type Mismatch" 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 the "Type Mismatch" error message:


   Call the function as follows: MyFunction (3).

   Function MyFunction (NumberToFind As integer)
      Dim MyDB As Database, MySet As Dynaset
      Set MyDB = CurrentDB()
      Set MySet = MyDB.CreateDynaset("Employees")
      MySet.FindFirst "[Employee ID] = '" & NumberToFind & "'"

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


   MySet.FindFirst "[Employee ID] = " & NumberToFind

Keep the following requirements in mind:

When the argument for an Access Basic 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 "[Hire Date] = #" & DateToFind & "#"

Numeric data types do not require delimiters, as in the following example:
```
      MySet.FindFirst "[Employee ID] = " & NumericDataToFind 
```

"1 Parameters Were Expected, But Only 0 Were Supplied" Error Message

You may receive this error message when you use the CreateDynaset method in Access Basic 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. The following sample code generates the error message when Query1 has the parameter "[Enter a Name]" in the Criteria for the Last Name field:


   Function TestQP ()
      Dim MyDB As Database, MySet As Dynaset
      Set MyDB = CurrentDB()
      Set MySet = MyDB.CreateDynaset("Query1")
      Debug.Print MySet![First Name]; Tab(10); MySet![Last Name]
   End Function

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


   Function TestQP ()
      Dim MyDB As Database, MySet As QueryDef, MyDyna As Dynaset
      Set MyDB = CurrentDB()
      Set MySet = MyDB.OpenQueryDef("Query1")
      MySet![Enter a Name] = "Davolio"
      Set MyDyna = MySet.CreateDynaset()
      Debug.Print MyDyna![First Name]; Tab(10); MyDyna![Last Name]
      MyDyna.Close
      MySet.Close
   End Function

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

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


   Set MySet = MyDB.CreateDynaset("SELECT * FROM Employees WHERE _
                    [Employee ID] = " & Forms!Form1!Field0 & ";")

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

Additional query words: parameters

Keywords : kberrmsg kbprg PgmObj
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbinfo