Access SQL INSERT INTO VALUES Syntax Error

[davidb88]

Hi Mr. Excel Forum -

I have a insert into statement and I am continuing to get a syntax error. I have reviewed the statement multiple times and still do not see where the issue is. I am sure it is a small punctuation error but because it is a rather long statement I can't figure out where the error is. Each of the variables that are outside of the quotations are predefined variables that are established earlier in the module.

Thanks in advance for anyone's help

strSQL = "INSERT INTO tblasc450f7f0 ( [Month], [Loan Type], [f7hpi50dpd1], [f7hpi50dpd2], [f7hpi50dpd3], [f7hpi50dpd4], [f7hpi50dpd5], [f7hpi80dpd1], [f7hpi80dpd2], [f7hpi80dpd3], [f7hpi80dpd4], [f7hpi80dpd5], [f7hpi100dpd1], [f7hpi100dpd2], [f7hpi100dpd3], [f7hpi100dpd4], [f7hpi100dpd5], [f7hpi125dpd1], [f7hpi125dpd2], [f7hpi125dpd3], [f7hpi125dpd4], [f7hpi125dpd5], [f7hpi130dpd1], [f7hpi130dpd2], " _
        & "[f7hpi130dpd3], [f7hpi130dpd4], [f7hpi130dpd5], [f8hpi50dpd1], [f8hpi50dpd2], [f8hpi50dpd3], [f8hpi50dpd4], [f8hpi50dpd5], [f8hpi80dpd1], [f8hpi80dpd2], [f8hpi80dpd3], [f8hpi80dpd4], [f8hpi80dpd5], [f8hpi100dpd1], [f8hpi100dpd2], [f8hpi100dpd3], [f8hpi100dpd4], [f8hpi100dpd5], [f8hpi125dpd1], [f8hpi125dpd2], [f8hpi125dpd3], [f8hpi125dpd4], [f8hpi125dpd5], [f8hpi130dpd1], [f8hpi130dpd2], [f8hpi130dpd3], " _
        & "[f8hpi130dpd4], [f8hpi130dpd5], [f9hpi50dpd1], [f9hpi50dpd2], [f9hpi50dpd3], [f9hpi50dpd4], [f9hpi50dpd5], [f9hpi80dpd1], [f9hpi80dpd2], [f9hpi80dpd3], [f9hpi80dpd4], [f9hpi80dpd5], [f9hpi100dpd1], [f9hpi100dpd2], [f9hpi100dpd3], [f9hpi100dpd4], [f9hpi100dpd5], [f9hpi125dpd1], [f9hpi125dpd2], [f9hpi125dpd3], [f9hpi125dpd4], [f9hpi125dpd5], [f9hpi130dpd1], [f9hpi130dpd2], [f9hpi130dpd3], [f9hpi130dpd4], " _
        & "[f9hpi130dpd5], [f10hpi50dpd1], [f10hpi50dpd2], [f10hpi50dpd3], [f10hpi50dpd4], [f10hpi50dpd5], [f10hpi80dpd1], [f10hpi80dpd2], [f10hpi80dpd3], [f10hpi80dpd4], [f10hpi80dpd5], [f10hpi100dpd1], [f10hpi100dpd2], [f10hpi100dpd3], [f10hpi100dpd4], [f10hpi100dpd5], [f10hpi125dpd1], [f10hpi125dpd2], [f10hpi125dpd3], [f10hpi125dpd4], [f10hpi125dpd5], [f10hpi130dpd1], [f10hpi130dpd2], [f10hpi130dpd3], [f10hpi130dpd4], [f10hpi130dpd5], " _
        & "[f0hpi50dpd1], [f0hpi50dpd2], [f0hpi50dpd3], [f0hpi50dpd4], [f0hpi50dpd5], [f0hpi80dpd1], [f0hpi80dpd2], [f0hpi80dpd3], [f0hpi80dpd4], [f0hpi80dpd5], [f0hpi100dpd1], [f0hpi100dpd2], [f0hpi100dpd3], [f0hpi100dpd4], [f0hpi100dpd5], [f0hpi125dpd1], [f0hpi125dpd2], [f0hpi125dpd3], [f0hpi125dpd4], [f0hpi125dpd5], [f0hpi130dpd1], [f0hpi130dpd2], [f0hpi130dpd3], [f0hpi130dpd4], [f0hpi130dpd5] ) " _
        & "VALUES ( #" & modeldate & "#, '1st Mtg', " & f7hpi50dpd1 & ", " & f7hpi50dpd2 & ", " & f7hpi50dpd3 & ", " & f7hpi50dpd4 & ", " & f7hpi50dpd5 & ", " & f7hpi80dpd1 & ", " & f7hpi80dpd2 & ", " & f7hpi80dpd3 & ", " & f7hpi80dpd4 & ", " & f7hpi80dpd5 & ", " & f7hpi100dpd1 & ", " & f7hpi100dpd2 & ", " & f7hpi100dpd3 & ", " & f7hpi100dpd4 & ", " & f7hpi100dpd5 & ", " & f7hpi125dpd1 & ", " & f7hpi125dpd2 & ", " & f7hpi125dpd3 & ", " & f7hpi125dpd4 & ", " & f7hpi125dpd5 & ", " & f7hpi130dpd1 & ", " & f7hpi130dpd2 & ", " _
        & " " & f7hpi130dpd3 & ", " & f7hpi130dpd4 & ", " & f7hpi130dpd5 & ", " & f8hpi50dpd1 & ", " & f8hpi50dpd2 & ", " & f8hpi50dpd3 & ", " & f8hpi50dpd4 & ", " & f8hpi50dpd5 & ", " & f8hpi80dpd1 & ", " & f8hpi80dpd2 & ", " & f8hpi80dpd3 & ", " & f8hpi80dpd4 & ", " & f8hpi80dpd5 & ", " & f8hpi100dpd1 & ", " & f8hpi100dpd2 & ", " & f8hpi100dpd3 & ", " & f8hpi100dpd4 & ", " & f8hpi100dpd5 & ", " & f8hpi125dpd1 & ", " & f8hpi125dpd2 & ", " & f8hpi125dpd3 & ", " & f8hpi125dpd4 & ", " & f8hpi125dpd5 & ", " & f8hpi130dpd1 & ", " & f8hpi130dpd2 & ", " & f8hpi130dpd3 & ", " _
        & " " & f8hpi130dpd4 & ", " & f8hpi130dpd5 & ", " & f9hpi50dpd1 & ", " & f9hpi50dpd2 & ", " & f9hpi50dpd3 & ", " & f9hpi50dpd4 & ", " & f9hpi50dpd5 & ", " & f9hpi80dpd1 & ", " & f9hpi80dpd2 & ", " & f9hpi80dpd3 & ", " & f9hpi80dpd4 & ", " & f9hpi80dpd5 & ", " & f9hpi100dpd1 & ", " & f9hpi100dpd2 & ", " & f9hpi100dpd3 & ", " & f9hpi100dpd4 & ", " & f9hpi100dpd5 & ", " & f9hpi125dpd1 & ", " & f9hpi125dpd2 & ", " & f9hpi125dpd3 & ", " & f9hpi125dpd4 & ", " & f9hpi125dpd5 & ", " & f9hpi130dpd1 & ", " & f9hpi130dpd2 & ", " & f9hpi130dpd3 & ", " & f9hpi130dpd4 & ", " _
        & " " & f9hpi130dpd5 & ", " & f10hpi50dpd1 & ", " & f10hpi50dpd2 & ", " & f10hpi50dpd3 & ", " & f10hpi50dpd4 & ", " & f10hpi50dpd5 & ", " & f10hpi80dpd1 & ", " & f10hpi80dpd2 & ", " & f10hpi80dpd3 & ", " & f10hpi80dpd4 & ", " & f10hpi80dpd5 & ", " & f10hpi100dpd1 & ", " & f10hpi100dpd2 & ", " & f10hpi100dpd3 & ", " & f10hpi100dpd4 & ", " & f10hpi100dpd5 & ", " & f10hpi125dpd1 & ", " & f10hpi125dpd2 & ", " & f10hpi125dpd3 & ", " & f10hpi125dpd4 & ", " & f10hpi125dpd5 & ", " & f10hpi130dpd1 & ", " & f10hpi130dpd2 & ", " & f10hpi130dpd3 & ", " & f10hpi130dpd4 & ", " & f10hpi130dpd5 & ", " _
        & " " & f0hpi50dpd1 & ", " & f0hpi50dpd2 & ", " & f0hpi50dpd3 & ", " & f0hpi50dpd4 & ", " & f0hpi50dpd5 & ", " & f0hpi80dpd1 & ", " & f0hpi80dpd2 & ", " & f0hpi80dpd3 & ", " & f0hpi80dpd4 & ", " & f0hpi80dpd5 & ", " & f0hpi100dpd1 & ", " & f0hpi100dpd2 & ", " & f0hpi100dpd3 & ", " & f0hpi100dpd4 & ", " & f0hpi100dpd5 & ", " & f0hpi125dpd1 & ", " & f0hpi125dpd2 & ", " & f0hpi125dpd3 & ", " & f0hpi125dpd4 & ", " & f0hpi125dpd5 & ", " & f0hpi130dpd1 & ", " & f0hpi130dpd2 & ", " & f0hpi130dpd3 & ", " & f0hpi130dpd4 & ", " & f0hpi130dpd5 & ");"
DoCmd.RunSQL (strSQL)

 

[Joe4]

Before running it, try returning it to the screen to see if it looks right with a Message Box, i.e.

MsgBox strSQL

Specifically, check the end. I thing strings may have a 255 character limit, which you may be exceeding (so your statement might be getting chopped off).

 

[davidb88]

You are right, Joe, it cuts it off at 255 characters. It would be helpful if Access let you know that before you kept trying to debug the code!

Do you have any recommendations on how I could get all of these variables appended to the same line of a table? If I made multiple attend queries is there a way to specifcy which line to append to?

Thanks for your help!

 

[Joe4]

Perhaps you may want to investigate adding the records via Recordsets in VBA code (DAO or ADO).

 

[AngelJ]

I didn't know about that 255 character limit. Does the Execute method have the same limitation?

I would try this first: CurrentDb.Execute strSQL

 

[davidb88]

Thanks, Joe. I was able to add in all of the records using DAO