Runtime error 3061 Too few parameters expected 1 with strSQL statement 2

[vba317]

I am getting the runtime error 3061 on an strSQL statement. I have been working on this all day and I have come up blank on why I am getting this error. I am hoping to get some help.
The debug statement results are:
INSERT INTO CONS_Rejections (uci,RptPd,SvcPd,AID,EncID,TranID,RevCode,ReviewDt,ReviewComment) SELECT SPN AS u,dbo_bi_Rejections.revpd,dbo_bi_Rejections.svcpd,dbo_bi_Rejections.aid,dbo_bi_Rejections.eid,dbo_bi_Rejections.slid,dbo_bi_Rejections.revcode,dbo_bi_Rejections.revdate,dbo_bi_Rejections.revcomment FROM dbo_bi_Rejections WHERE dbo_bi_Rejections.revpd>373 AND dbo_bi_Rejections.clntid=97;

Tom

strSQL = "INSERT INTO CONS_Rejections (uci,RptPd,SvcPd,AID,EncID,TranID,RevCode,ReviewDt,ReviewComment) " & _
         "SELECT " & strUCI & " AS u" & ",dbo_bi_Rejections.revpd,dbo_bi_Rejections.svcpd,dbo_bi_Rejections.aid,dbo_bi_Rejections.eid," & _
                 "dbo_bi_Rejections.slid,dbo_bi_Rejections.revcode,dbo_bi_Rejections.revdate,dbo_bi_Rejections.revcomment " & _
                 "FROM dbo_bi_Rejections " & _
                 "WHERE dbo_bi_Rejections.revpd>" & iFyStart & " AND dbo_bi_Rejections.clntid=" & iClid & ";"
                 Debug.Print strSQL
                 CurrentDB.Execute strSQL

 

[dhookom]

Is [uci] a text field? If so you would need to concatenate quotes into the statement. You can also get rid of the " As "

strSQL = "INSERT INTO CONS_Rejections (uci,RptPd,SvcPd,AID,EncID,TranID,RevCode,ReviewDt,ReviewComment) " & _
         "SELECT [COLOR=#EF2929]""[/color]" & strUCI & "[COLOR=#EF2929]""[/color] " & ",dbo_bi_Rejections.revpd, ...

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

Now I get a syntax error

"SELECT """ & strUCI & """ & """ AS u """ & ",dbo_bi_Rejections.revpd,

 

[PHV]

"SELECT '" & strUCI & "' AS u,dbo_bi_Rejections.revpd,dbo_bi_Rejections.svcpd,dbo_bi_Rejections.aid,dbo_bi_Rejections.eid," & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

vba317,
You are possibly going to get error messages if you don't apply the changes as suggested.

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

Now I am getting Error 3346 number of query values and destination fields are not the same.

debugSQL results

INSERT INTO CONS_Rejections (uci,RptPd,SvcPd,AID,EncID,TranID,RevCode,ReviewDt,ReviewComment) SELECT 'SPN' AS u,dbo_bi_Rejections.r,dbo_bi_Rejections.revpd,dbo_bi_Rejections.svcpd,dbo_bi_Rejections.aid,dbo_bi_Rejections.eid,dbo_bi_Rejections.slid,dbo_bi_Rejections.revcode,dbo_bi_Rejections.revdate,dbo_bi_Rejections.revcomment FROM dbo_bi_Rejections rWHERE dbo_bi_Rejections.revpd>373 AND dbo_bi_Rejections.clntid=97;

 

[dhookom]

Your insert has 9 fields and the select has 10. Your debug.print also contains " rWHERE "

I would also get rid of the " AS u " and all of the "dbo_bi_Rejections." since when you have only one table in the SELECT FROM the table name is not required.

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

It was a spelling error. I just got it to work. Thanks for all your help!

strSQL = "INSERT INTO CONS_Rejections (uci,RptPd,SvcPd,AID,EncID,TranID,RevCode,ReviewDt,ReviewComment) " & _
                 "SELECT '" & strUCI & "'" & "AS u,dbo_bi_Rejections.revpd,dbo_bi_Rejections.svcpd,dbo_bi_Rejections.aid,dbo_bi_Rejections.eid," & _
                 "dbo_bi_Rejections.slid,dbo_bi_Rejections.revcode,dbo_bi_Rejections.revdate,dbo_bi_Rejections.revcomment " & _
                 "FROM dbo_bi_Rejections " & _
                 "WHERE dbo_bi_Rejections.revpd>" & iFyStart & " AND dbo_bi_Rejections.clntid=" & iClid & ";"

 

[dhookom]

Glad to hear you got this working. My code would be a little more compact:

strSQL = "INSERT INTO CONS_Rejections " & _
     "(uci, RptPd, SvcPd, AID, EncID, TranID, RevCode, ReviewDt, ReviewComment) " & _
     "SELECT '" & strUCI & "' ,revpd, svcpd, aid, eid, slid, revcode, revdate, revcomment " & _
     "FROM dbo_bi_Rejections " & _
     "WHERE revpd > " & iFyStart & " AND clntid = " & iClid & ";"

Duane
Hook'D on Access
MS Access MVP