Solved

Runtime Error 3070

Posted on 2001-08-23
7
2,177 Views
Last Modified: 2007-11-27
We just recently switched from Access 95 to Access 2000 and have a user who keeps getting a Runtime Error 3070.  Has anyone run into this problem and fixed it.  If so how?  Not sure there is an answer if there is I will adjust the points.

M!
0
Comment
Question by:flfmmqp
7 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 6419508
From MSKB:

ACC2000: Errors Concatenating Variables or Controls

--------------------------------------------------------------------------------
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 must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and ensure that the Microsoft DAO 3.6 Object Library check box is selected.

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 Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please see the following page on the World Wide Web:
http://www.microsoft.com/partner/referral/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://support.microsoft.com/directory/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 run time error 3070 run time error 3464 run time error 3061

Keywords : kberrmsg kbprogramming kbdta
Issue type : kbhowto
Technology : kbAccessSearch kbAccess2000 kbAccess2000Search

HTH,
Paasky
0
 

Author Comment

by:flfmmqp
ID: 6419544
MSKB:

Thanks for the quick response.  Problem is that I only have one user with this problem and 12 who do not.  So I don't think it is a coding issue.  If it didn't work on all 13 workstations then I could see that being the problem

Michael
0
 

Author Comment

by:flfmmqp
ID: 6419545
MSKB:

Thanks for the quick response.  Problem is that I only have one user with this problem and 12 who do not.  So I don't think it is a coding issue.  If it didn't work on all 13 workstations then I could see that being the problem

M!
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 10

Expert Comment

by:paasky
ID: 6420813
Could there be corrupted or missing system/library file in the problem machine? Maybe you could try re-installing Access 2000...
0
 

Author Comment

by:flfmmqp
ID: 6422119
Paasky:

Just tried that same stupid error.  Augh.

M!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6990344
for flfmmqp

It's time to clean up this TA, so I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts refunded
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7005131
Per recommendation, points refunded and question closed by
Netminder
CS Moderator
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question