Run-Time Error 3134 - Syntax Error in Insert Into Statement

[davidb88]

Hi -

I have an Insert Into Statement in my VBA code and I keep getting a run-time error 3134. I know there are a number of posts about this type of problem and I have tried all of the solutions I have found but nothing seems to work for this particular string. If someone sees the issue, can you please point it out? Thanks.

strSQL = "INSERT INTO tblPDcalc ([Month End], [Loan Type], [FICO Band], [r1PD], [r2PD], [r3PD], [r4PD], [r5PD], " _
        & "SELECT (#" & modeldate & "#, '1st Mtg', '1', " _
        & " " & r1PD & ", " & r2PD & ", " & r3PD & ", " & r4PD & ", " & r5PD & ");"


DoCmd.RunSQL (strSQL)

 

[mole999]

double speech marks on beginning of line three look suspicious to me

 

[Norie]

Why are you using SELECT when the values you are inserting aren't coming from a table/query.

This is the syntax for inserting values:

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

 

[xenou]

You are missing a closing parentheses (and have an extra comma too, I think).

Are the value you are "selecting" supposed to come from a table, or are they literal values. It looks like literal values. The correct sql should be something like:

[COLOR="#000080"]INSERT INTO[/COLOR] TblPDCalc ( [I]Field1[/I], [I]Field2[/I], [I]Field3[/I], .... )
[COLOR="#000080"]VALUES [/COLOR]( [I]Value1[/I], [I]Value2[/I], [I]Value3[/I], ... )

 

[davidb88]

Thanks for the responses. I was able to get the code to work now! Here is the final code in case anyone else needs something similar:

'Append PD calculations to tblPDcalc
strSQL = "INSERT INTO tblPDcalc ([Month End], [Loan Type], [FICO Band], [r1PD], [r2PD], [r3PD], [r4PD], [r5PD]) " _
        & "VALUES (#" & modeldate & "#, '1st Mtg', '1', " & r1PD & ", " & r2PD & ", " & r3PD & ", " & r4PD & ", " & r5PD & ");"


DoCmd.RunSQL (strSQL)