Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Runtime Error 3070

Posted on 2001-08-23
7
Medium Priority
?
2,200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

721 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