Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    ULMA is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Feb 2015
    Posts
    14

    Syntax Error In Insert Into Statement

    Hi there,
    I am new to this forum; I would appreciate anyone's input in solving a problem of syntax error with the following code:


    Dim dbs As DAO.Database, strSql As String
    Set dbs = CurrentDb
    strSql = ""
    strSql = "INSERT INTO eventlog "
    strSql = strSql & "(date, accountno, approvalcode, order, sku, price, discount, code, event)"


    strSql = strSql & " Values"
    strSql = strSql & "('" & Date() & "', "
    strSql = strSql & " '" & Me!AccountNo & "', "
    strSql = strSql & "'" & strSeek & "',"
    strSql = strSql & "'" & Me!ReturnNo & "', "
    strSql = strSql & "'" & Me!Combo33 & "', "
    strSql = strSql & "0, "
    strSql = strSql & "0, "
    strSql = strSql & "0, "
    strSql = strSql & "'" & "Non Returnable Item" & "'" & ");"
    'Debug.Print strSQL
    dbs.Execute strSql

    Thanks to all!

  2. #2
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Depends on where the syntax error is If it's on one of the strsql = lines It could be the use of the single quote. VBA sees everything after the ' as a comment. I've never tried using it as you do so I'm not 100% sure that won't work but I don't think so. If the error is on the line strSql = strSql & "('" & Date() & "', " I would bet it's the single quote if not then Assuming it's the dbs.Execute strsql I still think it's the use of single quotes but in the SQL Syntax not VBA. For example

    Assuming accountno is numeric, code is numeric, order is string, sku is string, discount is numeric, and codeevent is string

    Your strsql should look like this when the execute statement is run

    Insert Into sometable (date,accountno,code,order,sku,price,discount,code event) Values (#02/24/2014#,2014022400001,1,"order 1","abdee",.20,"this is a test event")


    Put a breakpoint at the dbs.Execute strsql and see what the strsql statement looks like.

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I think it may be here:

    strSql = strSql & "('" & Date() & "', "

    You have the Date() string enclosed in single quotes which should not be there. The result is you are trying to assign text data to a date field.

  4. #4
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Nice catch John I totally spaced on the date field there.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ulma,
    I agree that dates should be within #datefield#.
    Take the quote off the debug.print and see what's in strSQL

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The apostrophe is appropriate delimiter for text field parameters. It follows a quote mark and is not at beginning of line so it won't be comment indicator.

    Use # to delimit date parameters.

    No delimiters for number type field parameters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ULMA is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Feb 2015
    Posts
    14
    Thanks for the suggestion; I tried # but still getting same error. By the way I am working with Access 97

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Sorry, Ulma, I didn't have it quite right, either.

    This line: strSql = strSql & "('" & Date() & "', "

    should look like this: strSql = strSql & "(#" & Date() & "#, "


  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    AFAIK, same rules.

    Post the revised statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    If you are still getting the syntax error, uncomment the debug.print line, and show us what the strSQL contains.

    Another possibility is AccountNo - what type of data is it? If it is numeric, then you need:

    strSql = strSql & Me!AccountNo & ", "

    without the single quotes.
    Last edited by June7; 02-24-2015 at 12:36 PM. Reason: additional comment

  11. #11
    ULMA is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Feb 2015
    Posts
    14
    My strSQL shows as follows:
    INSERT INTO eventlog (date, accountno, approvalcode, order, sku, price, discount, code, event) Values(#2/24/15#, '113A230', '45678','16722', '200-7-9341',0,0,0,'Non Returnable Item');

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are approvalcode and order text type fields? Is code a number type field?

    Why is there 2-digit year? Access should assume 2015 but why doesn't it show 2015?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I don't see anything obvious. The only thing I can think of is that approvalcode or order (or both) is numeric, in which case you don't need the quotes, or that your field called Date (which should not be called that - use OrderDate for example) is not Date/Time type.

    The table you are appending to IS called eventlog?

  14. #14
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    June -

    Why is there 2-digit year? Access should assume 2015 but why doesn't it show 2015?
    Probably the short date setting on the Region and Language section of the Control Panel.

    J.

  15. #15
    ULMA is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Feb 2015
    Posts
    14
    This is data type:

    Date (Date/Time) Default now()
    AccountNo (Text)
    ApprovalCode (Text)
    Order (Number)
    SKU (Text)
    Price (Number)
    Discount (Number)
    Code (Number)
    Event (Text)

    strSeek is an input box string variable

    These are the latest changes I have done since beginning of this thread:
    Dim dbs As DAO.Database, strSql As String
    Set dbs = CurrentDb
    strSql = ""
    strSql = "INSERT INTO eventlog"
    strSql = strSql & "(date, accountno, approvalcode, order, sku, price, discount, code, event)"
    strSql = strSql & " Values"
    strSql = strSql & "(#" & Now() & "#,"
    strSql = strSql & "'" & Me!AccountNo & "',"
    strSql = strSql & "'" & strSeek & "',"
    strSql = strSql & Me!ReturnNo & ","
    strSql = strSql & "'" & Me!Combo33 & "',"
    strSql = strSql & 0 & ","
    strSql = strSql & 0 & ","
    strSql = strSql & 0 & ","
    strSql = strSql & "'" & "Non Returnable Item" & "'" & ");"
    'Debug.Print strSql
    MsgBox strSql
    dbs.Execute strSql

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-05-2013, 11:17 AM
  2. Syntax Error: Insert Into statement in VBA
    By Kimbertha in forum Programming
    Replies: 1
    Last Post: 07-24-2012, 05:02 PM
  3. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  4. INSERT INTO Syntax Error
    By eww in forum Programming
    Replies: 1
    Last Post: 04-06-2011, 10:28 AM
  5. Syntax Error In Insert Into Statement
    By frankvfox in forum Queries
    Replies: 1
    Last Post: 09-09-2008, 12:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums