SQL Subquery problem

[yenni]

What is wrong with this code?
I keep getting ERROR# 3061...Too few parameters, Expected 2
Can anyone help me?

Set rst = dbs.OpenRecordset("SELECT recno, ItemNum, Descrip, QtyRecvd, UnitPrice" _
& " FROM RecLine" _
& " WHERE recno" _
& " IN (SELECT recno FROM RecHdr" _
& " WHERE DateRecvd Between frmprtaudit.dtpicker1" _
& " And frmprtaudit.dtpicker2)", dbOpenDynaset)

[PaulLewis]

But one that I can spot straight away is quite a common error I have seen repeated in questions in the Database section:

In your code, where you have the subquery clause,

& " WHERE DateRecvd Between frmprtaudit.dtpicker1" _ 
& " And frmprtaudit.dtpicker2)", dbOpenDynaset)

How does the DB engine know what "frmprtaudit.dtpicker1" or "frmprtaudit.dtpicker2" are?

You will need to place the correct type of value in here at the very least. I recommend

& " WHERE DateRecvd Between #" & frmprtaudit.dtpicker1 _ 
& "# And #" & frmprtaudit.dtpicker2 & "#)", dbOpenDynaset)

If you ever have this sort of error, just debug the line by first placing your sql query into a string, print the string, then attempt to run the query. You would have noticed the problem yourself if you had done this...

Cheers

I get that error all the time. It means you've probably mis-spelled one of the variable names.

Cut the code to clipboard, invoke Access with database that this is based on. In the queries section, get a new one, cancel out of the table, right-click on the blue QBE bar and select sql code.

paste the code from your program in here, and you'll get a much better error message.

Good Luck
DerFarm

[yenni]

Thanks a million PaulLewis. Your solution worked perfectly.
I really appreciate your reply.
Once again, THANKS