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

davidb88

Board Regular
Joined
Sep 23, 2013
Messages
76
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.

Code:
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)
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,359
Office Version
2019, 2016, 2013
Platform
Windows
double speech marks on beginning of line three look suspicious to me
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,599
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,667
Office Version
2013
Platform
Windows
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:

Code:
[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

Board Regular
Joined
Sep 23, 2013
Messages
76
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:
Code:
'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)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,101,785
Messages
5,482,913
Members
407,368
Latest member
FunkyFriedChicken

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top