Solved

Runtime Error 3070

Posted on 2001-08-23
7
2,190 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

734 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