Results 1 to 6 of 6

Thread: VBA SQL Syntax Woes
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2012
    Posts
    245
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA SQL Syntax Woes

    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

  2. #2
    Board Regular
    Join Date
    Apr 2013
    Location
    Riyadh
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA SQL Syntax Woes

    Hi, Please try the below for the Date Fields with value.

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

    Cheers
    Chard

  3. #3
    Board Regular
    Join Date
    Mar 2012
    Posts
    245
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA SQL Syntax Woes

    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?

  4. #4
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,642
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA SQL Syntax Woes

    ", " & "'" & Sheet28.Cells(lRow, 8) & ", "

    Should be

    ", " & "'" & Sheet28.Cells(lRow, 8) & "', "

  5. #5
    Board Regular
    Join Date
    Mar 2012
    Posts
    245
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA SQL Syntax Woes

    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.

  6. #6
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,642
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA SQL Syntax Woes

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •