Error: too few Parameters. Expected 3...while executing SQL statement in Access

Question

I've done this before, just a test as to whether a record already exists in a table before I insert a new record. This was basically what I did, yet this time I get an error

Error: "3061 Too few parameters. Expected 2."

I tried to be very specific (tblfromICPMS.Woid instead of just Woid) thinking that might help, but it didn't really make a difference. I get the specifically at the line

If rs.RecordCount = 0 Then

This is the code, where ThisWoid, Analyte and ThisTestId are local variables:

DIM rs As Object strSQL = "SELECT tblfromICPMS.Woid, tblfromICPMS.Analyte, tblfromICPMS.TestID FROM tblFROMICPMS WHERE (tblFROMICPMS.woid = " & ThisWoid & ") AND (tblFROMICPMS.analyte = " & Analyte & ") AND (tblfromICPMS.Testid=" & ThisTestID & ") " Set rs = CurrentDb.OpenRecordset(strSQL) If rs.RecordCount = 0 Then "insert record" End IF

UPDATE: error message changed to 1 expected after updating sqlstr in comments, also included declaration of rs. For clarification tblFromICPMS is a table which contains the columns Woid, Analyte and TestId. I have retrieved three values and stored them in local variables ThisWoid, Analyte and ThisTestId. I want to insert a record but first I must see if it already exists in tblfromICPMS. Also ThisWoid and Analyte are strings

Have you tried to debug? I am afraid, in what you posted here, you don't have a line that trows error. And what is this? (tblFROMICPMS.woid = ThisWoid). What is ThisWoid. Isn't it supposed to be (tblFROMICPMS.woid = " & ThisWoid & ")"? — T.S.
– T.S., Commented Jun 27, 2014 at 16:16
Have a look at TabelfromICPMS.TestID. That should be tblfromICPMS.TestID instead. — VMai
– VMai, Commented Jun 27, 2014 at 16:19
Are you concatenating that sql string in the actual code? If you step through, you will see the statement is literally SELECT tblfromICPMS.Woid, tblfromICPMS.Analyte, TabelfromICPMS.TestID FROM tblFROMICPMS WHERE (tblFROMICPMS.woid = ThisWoid) AND (tblFROMICPMS.analyte = Analyte) AND (tblfromICPMS.Testid=ThisTestID) where you want something like "WHERE (tblFromICPMS.woid = " & strVariable & ") AND " ` — Jimmy Smith
– Jimmy Smith, Commented Jun 27, 2014 at 16:23
Basically, we all agree that you should try this string "SELECT tblfromICPMS.Woid, tblfromICPMS.Analyte, tblfromICPMS.TestID FROM tblFROMICPMS WHERE (tblFROMICPMS.woid = " & ThisWoid & ") AND (tblFROMICPMS.analyte = " & Analyte & ") AND (tblfromICPMS.Testid=" & ThisTestID & ") " And also, if any of your conditions are compared to string you need a single quote on each side as "... ='" & var & "' and ..." — T.S.
– T.S., Commented Jun 27, 2014 at 16:32

Johnny Bones · Accepted Answer · 2014-06-27 17:09:58Z

First of all, I'd try DIMing the rs as a RecordSet, not an Object. Also, stick in a Message Box just to make sure your SQL string makes sense:

DIM rs As Recordset strSQL = "SELECT Woid, Analyte, TestID FROM tblFROMICPMS WHERE (woid = " & ThisWoid & ") AND (analyte = " & Analyte & ") AND (Testid=" & ThisTestID & ") " msgbox strSQL Set rs = CurrentDb.OpenRecordset(strSQL) If rs.RecordCount = 0 Then "insert record" End IF

See if the message box results give you any clues as to what's wrong. Typically, that error happens when Access doesn't understand one of your variables.

Don't forget, text needs to be surrounded by single quotes in your SQL string, integers do not.

Collectives™ on Stack Overflow

Error: too few Parameters. Expected 3...while executing SQL statement in Access

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related