VBA SQL Syntax Woes

TerryHogarth21

Board Regular
Joined
Mar 20, 2012
Messages
245
I'm creating a SQL string in VBA to insert to a SQL table.

This syntax string is driving me nuts as I have an inaccurate syntax of an apostrophe or extra quotation somewhere when verifying each of the values
Code:
 sSQL = sSQL & " VALUES (" & _
"'" & Sheet28.Cells(lRow, 1) & "', " & "'" & Sheet28.Cells(lRow, 2) & ", " & "'" & Sheet28.Cells(lRow, 3) & "', " & "'" & Sheet28.Cells(lRow, 4) & "', " & "'" & Sheet28.Cells(lRow, 5) & "', " & "'" & Sheet28.Cells(lRow, 6) & "', " & "'" & Sheet28.Cells(lRow, 7) & "', " & "'" & Sheet28.Cells(lRow, 8) & "', " & "'" & Sheet28.Cells(lRow, 9) & "', " & _


I want to ensure also that for the field types with data including Numbers in the SQL table and Excel I just need it to look like this
Code:
 Sheet28.cells(lRow, 1)
and for Text values it should include the apostrophe like this
Code:
 "'" & Sheet28.cells(lRow, 1) & "'"
.

For date fields that have values I think it's serial in Excel but date/time in SQL table so I'm assuming I don't need the apostrophe?

If anybody can clarify that would be great!

Thanks
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi, Please try the below for the Date Fields with value.

"#" & Sheet28.cells(lRow, 1) & "#"

Cheers
Chard
 
Upvote 0
Ok made the changes to include "#" for date fields but getting the error somewhere in the 2nd line.
Code:
   sSQL = sSQL & " VALUES ("
    sSQL = sSQL & Sheet28.Cells(lRow, 1) & ", " &  Sheet28.Cells(lRow, 2) & ", " & Sheet28.Cells(lRow, 3) & ", "  & Sheet28.Cells(lRow, 4) & ", " & Sheet28.Cells(lRow, 5)  & ", " & Sheet28.Cells(lRow, 6) & ", " &  Sheet28.Cells(lRow, 7) & ", " & "'" & Sheet28.Cells(lRow, 8)  & ", " & "'" & Sheet28.Cells(lRow, 9) & "', "

Got a feeling it's this line
Code:
 sSQL = sSQL & sheet28.cells(lRow,1)  & ","
since that's the first field that's a number it doesn't need the apostrophe but am I missing a quotation or something after joining it with the prior sql string?
 
Upvote 0
", " & "'" & Sheet28.Cells(lRow, 8) & ", "

Should be

", " & "'" & Sheet28.Cells(lRow, 8) & "', "
 
Upvote 0
Success! thanks Kyle123

- Also to clarify you do not need '#' for date/time format in the vba syntax to get it into a sql statement.
 
Upvote 0
Whether you need the # very much depends on your database. Access needs it whereas SQL server for example stores dates as strings. It is important though to always always pass dates in the format yyyy-mm-dd
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top