Solved MS Access SQL too few parameters 3061 "2" (1 Viewer)

Jallan!

New member
Local time
Today, 02:14
Joined
Jan 31, 2022
Messages
10
This is my code below at the bottom of this post; 4 hours of headbanging and I cannot figure it out. I have one source table and one target table. Three fields in the sub query are to be used to filter the resulting recordset.
The source table contains some records already in the target table, so I do not want to process those records.
I only want to process records in source table ES003CBP that are not in target table ES003EIN

The problem I discovered by using the QBE to debug my code is that the fields in the subquery (in bold below) are expecting a parameter to be passed to them so the query will run, but I want all missing records in the target table resident in the source table, not records that are the result of a parm that I pass to the query. Because these query fields are not populated with the parm the subquery is expecting, I get the 3061 error.

Once I obtain the result set of records in the source table not being present in the target table, I want to manipulate data and then write the new record into the target table. Intresting side note, I have three fields used to join the two tables together but the 3061 error says 2.

Source table: ES003CBP
Target table: ES003EIN
Fields expecting values as passed parms:
[ES003CBP].[ENTRYSUMMARYLINE
[ES003EIN].[ENTRYSUMMARYLINE]
[ES003CBP].[ENTRYSUMMARYLINENUMBER]
[ES003EIN].[ENTRYSUMMARYLINENUMBER]
[ES003CBP].[TARIFFORDINALNUMBER]
[ES003EIN].[TARIFFORDINALNUMBER]


Do I need to somehow force these values so they are not expected to be parms, but are criteria necessary to return only records from the source table?

Set rstsrc = db.OpenRecordset("SELECT * FROM ES003CBP WHERE NOT EXISTS " & _
(SELECT * FROM ES003EIN WHERE ([ES003CBP].[ENTRYSUMMARYLINE] = [ES003EIN].[ENTRYSUMMARYLINE]) " & _
AND " & _
"([ES003CBP].[ENTRYSUMMARYLINENUMBER] = [ES003EIN].[ENTRYSUMMARYLINENUMBER]) AND " & _
"([ES003CBP].[TARIFFORDINALNUMBER] = [ES003EIN].[TARIFFORDINALNUMBER])) " & _

"ORDER BY ENTRYSUMMARYNUMBER, ENTRYSUMMARYLINENUMBER, TARIFFORDINALNUMBER")"
 

Ranman256

Well-known member
Local time
Today, 05:14
Joined
Apr 9, 2015
Messages
4,339
if you are using vb code instead of a query, (use a query),you must assign the parameters using the parameter object.
if you dont have parameters, then you have a field that doesnt exist, or its spelled wrong.
 

Jallan!

New member
Local time
Today, 02:14
Joined
Jan 31, 2022
Messages
10
Thank you. I am using SQL (see code), I triple checked spelling. I have four hours of debug time on this and need help please.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:14
Joined
May 21, 2018
Messages
8,463
How about doing everyone a favor and at least show how the sql string resolves. That would be a PITA to try to figure out
Code:
Dim strSql as string
strSql = "SELECT * FROM ES003CBP WHERE NOT EXISTS (SELECT * FROM ES003EIN WHERE ([ES003CBP].[ENTRYSUMMARYLINE] = [ES003EIN].[ENTRYSUMMARYLINE]) AND ([ES003CBP].[ENTRYSUMMARYLINENUMBER] = [ES003EIN].[ENTRYSUMMARYLINENUMBER]) AND ([ES003CBP].[TARIFFORDINALNUMBER] = [ES003EIN].[TARIFFORDINALNUMBER])) ORDER BY ENTRYSUMMARYNUMBER, ENTRYSUMMARYLINENUMBER, TARIFFORDINALNUMBER")"
debug.print strSql
 

plog

Banishment Pending
Local time
Today, 04:14
Joined
May 11, 2011
Messages
11,613
I don't use EXIST and NOT EXIST, so I googled its correct use and everything I found has the sub query only using "SELECT 1 FROM..." and not "SELECT * FROM...". Something about it not knowing what to do with actual query results instead of just the value 1 which resolves to True.

The reason I don't use EXIST and NOT EXIST is because I think you can always acheive the same thing with a LEFT JOIN and NULL Criteria:

Code:
SELECT *
FROM A
LEFT JOIN B ON B.Field1=A.Field1 AND B.Field2=A.Field2
WHERE B.PrimaryKeyField IS NULL

That's logically equivalent to your query (show records in A without a match in B). Give it a shot and see if you still get the error. Or just change your existing sub query * with 1
 

Jallan!

New member
Local time
Today, 02:14
Joined
Jan 31, 2022
Messages
10
Apologies. I am new to this. I cannot get past the error so I can use debug.print. If you tell me what I need to do to show how the SQL string resolves, I will do that. No way do I want this to be PITA for anyone. I am desperate.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:14
Joined
May 21, 2018
Messages
8,463
Code:
dim strSql as string
strSql = "SELECT * FROM ES003CBP WHERE NOT EXISTS (SELECT * FROM ES003EIN WHERE ([ES003CBP].[ENTRYSUMMARYLINE] = [ES003EIN].[ENTRYSUMMARYLINE]) " & _
AND ([ES003CBP].[ENTRYSUMMARYLINENUMBER] = [ES003EIN].[ENTRYSUMMARYLINENUMBER]) AND ([ES003CBP].[TARIFFORDINALNUMBER] = [ES003EIN].[TARIFFORDINALNUMBER])) " & _
"ORDER BY ENTRYSUMMARYNUMBER, ENTRYSUMMARYLINENUMBER, TARIFFORDINALNUMBER")"
debug.print strsql
'Post actual result
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:14
Joined
May 7, 2009
Messages
19,170
same as in post#5
Code:
SELECT T1.* FROM
ES003CBP AS T1
LEFT JOIN
ES003EIN AS T2
ON
T1.ENTRYSUMMARYLINE = T2.ENTRYSUMMARYLINE AND
T1.ENTRYSUMMARYLINENUMBER = T2.ENTRYSUMMARYLINENUMBER AND
T1.TARIFFORDINALNUMBER = T2.TARIFFORDINALNUMBER
WHERE ((T2.ENTRYSUMMARYLINE) Is Null);
 

Jallan!

New member
Local time
Today, 02:14
Joined
Jan 31, 2022
Messages
10
Thank you for the help, but I still have errors, albeit new errors. When I loaded the SQL into the Access query builder it asked for parameters to be loaded for the fields I list for the join, in bold:

Set rstsrc = db.OpenRecordset("SELECT * FROM ES003CBP left outer join es003ein on [ES003ein].[ENTRYSUMMARYLINE] = [ES003cbp].[ENTRYSUMMARYLINE]" & _
" where es003ein.esunique is Null")

1643988332933.png
 

SHANEMAC51

Active member
Local time
Today, 12:14
Joined
Jan 28, 2022
Messages
310
[ES003CBP].[ENTRYSUMMARYLINE
[ES003EIN].[ENTRYSUMMARYLINE]
[ES003CBP].[ENTRYSUMMARYLINENUMBER]
[ES003EIN].[ENTRYSUMMARYLINENUMBER]
[ES003CBP].[TARIFFORDINALNUMBER]
[ES003EIN].[TARIFFORDINALNUMBER]
what uncomfortable names do you have, I prefer absolutely unreadable
ENTRY_SUMMARY_ LINE
 

Jallan!

New member
Local time
Today, 02:14
Joined
Jan 31, 2022
Messages
10
Sorry Shanemac51, I am new to code. Do you have a solution for me? I am at a dead end
 

plog

Banishment Pending
Local time
Today, 04:14
Joined
May 11, 2011
Messages
11,613
Divide, isolate and conquer. Just get something to work:

Code:
SELECT * FROM ES003CBP

Does the above query work in the designer? If not, you found out where to look. If it does work, add 1 thing back to it and test again. Whenever it breaks whatever the last thing you added is the culprit.
 

SHANEMAC51

Active member
Local time
Today, 12:14
Joined
Jan 28, 2022
Messages
310
Sorry Shanemac51, I am new to code. Do you have a solution for me? I am at a dead end
Code:
Sub mm220204()
Dim db As DAO.Database
Dim rstsrc As DAO.Recordset
Dim s1
s1 = "select * from es003cbp"
s1 = s1 & " where not exists "
s1 = s1 & "(select * from es003ein"
s1 = s1 & " where ([es003cbp].[entrysummaryline]     = [es003ein].[entrysummaryline]) "
s1 = s1 & " and ([es003cbp].[entrysummarylinenumber] = [es003ein].[entrysummarylinenumber]) "
s1 = s1 & " and ([es003cbp].[tariffordinalnumber]    = [es003ein].[tariffordinalnumber])"
s1 = s1 & " order by entrysummarynumber, entrysummarylinenumber, tariffordinalnumber)"
Set db = CurrentDb
Set rstsrc = db.OpenRecordset(s1)
End Sub
 

Users who are viewing this thread

Top Bottom