Solved

Runtime Error 3070

Posted on 2001-08-23
7
2,152 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now