INSERT INTO statement not working in VBA

[Kheribus]

Hello,

I am attempting to execute some code to insert values into an EMPTY linked MySQL table, 'modifications'.

Here is the table structure:
CREATE TABLE `modifications` (
`tripName` varchar(120) NOT NULL,
`coNo` varchar(30) NOT NULL,
`modDate` date NOT NULL,
`modType` varchar(120) NOT NULL,
`paid` int(11) DEFAULT NULL,
`total` double DEFAULT NULL,
PRIMARY KEY (`tripName`,`modType`,`modDate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Inside my code, this statement will successfully insert into the table:

DoCmd.RunSQL ("INSERT INTO modifications (tripName, coNo, modDate, modType, paid, total) VALUES ('CS011', 'FO-13022', '2015-01-01', 'CHG', 0, 121212.00)")

However, when I try to use my dynamic variables to execute the statement, I can't get anything to actually populate in the table.

I have tried just about every type of date format that I can try, passing without formats, using single quote, #, no single quote, no format, and nothing will work.

DoCmd.RunSQL ("INSERT INTO modifications ([tripName], [coNo], [modDate], [modType], [paid], [total]) VALUES ('" & shortTripName1 & "', '" & rsTemp!coNo & "', '" & invoiceDateArray(i) & "', '" & modtype & "', " & 0 & ", " & balanceDif)

DoCmd.RunSQL ("INSERT INTO modifications ([tripName], [coNo], [modDate], [modType], [paid], [total]) VALUES ('" & shortTripName1 & "', '" & rsTemp!coNo & "', " & invoiceDateArray(i) & ", '" & modtype & "', " & 0 & ", " & balanceDif)
                                
DoCmd.RunSQL ("INSERT INTO modifications ([tripName], [coNo], [modDate], [modType], [paid], [total]) VALUES ('" & shortTripName1 & "', '" & rsTemp!coNo & "', #" & invoiceDateArray(i) & "#, '" & modtype & "', " & 0 & ", " & balanceDif)
                                
DoCmd.RunSQL ("INSERT INTO modifications ([tripName], [coNo], [modDate], [modType], [paid], [total]) VALUES ('" & shortTripName1 & "', '" & rsTemp!coNo & "', '" & Format(invoiceDateArray(i), "YYYY/MM/DD") & _
"', '" & modtype & "', " & 0 & ", " & balanceDif)

DoCmd.RunSQL ("INSERT INTO modifications ([tripName], [coNo], [modDate], [modType], [paid], [total]) VALUES ('" & shortTripName1 & "', '" & rsTemp!coNo & "', #" & Format(invoiceDateArray(i), "YYYY/MM/DD") & _
"#, '" & modtype & "', " & 0 & ", " & balanceDif)

DoCmd.RunSQL ("INSERT INTO modifications ([tripName], [coNo], [modDate], [modType], [paid], [total]) VALUES ('" & shortTripName1 & "', '" & rsTemp!coNo & "', " & Format(invoiceDateArray(i), "YYYY/MM/DD") & _
", '" & modtype & "', " & 0 & ", " & balanceDif)

The values being passed are the same as I entered in the successful test statement at the top....

shortTripName1: "CS11"
rsTemp!coNo: "FO-13022"
invoiceDateArray(i) as a date array : 01/05/2016
modtype: "CHG"

Any ideas why this isn't passing correctly?

 

[pbaldy]

Here's a debugging method many of us use in these situations:

http://www.baldyweb.com/ImmediateWindow.htm

 

[Kheribus]

Thanks! I'll give it a shot!

 

[pbaldy]

No problem. By the way, for a date/time field the proper delimiter in Access is #.

 

[Kheribus]

Thank you so much! I can't believe that I haven't been using the immediate window and that trick...

It made it absolutely obvious that I was just missing a closing parenthesis after my last variable in the RunSQL statement.

You rock!

 

[pbaldy]

Happy to help!