Solved MS Access SQL too few parameters 3061 "2"

[Jallan!]

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]

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!]

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]

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

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]

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:

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!]

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]

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]

same as in post#5

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!]

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")

 

[SHANEMAC51]

[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!]

Sorry Shanemac51, I am new to code. Do you have a solution for me? I am at a dead end

 

[plog]

Divide, isolate and conquer. Just get something to work:

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]

Sorry Shanemac51, I am new to code. Do you have a solution for me? I am at a dead end

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