ACC1x: Error Messages When Concatenating Variables or Controls

• 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

• 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.

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

   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 & "'"

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

• 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

   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

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 & ";")