Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 33
  1. #16
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    what does your debug.print strsql shhow when you uncomment it?

    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');"



    edit:
    sorry more explanation, you are inserting a text string into your table that is static (Non Returnable Item) you don't need anything fancy, just the string in ' marks or "" marks (you need two double quotes to indicate a single double quote as part of a text string).

  2. #17
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If order is a number type, should not have apostrophe delimiters.
    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.

  3. #18
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I still don't see a problem.

    What is the exact text of the error message you get? The term "syntax error" can occur in several messages.

    try using dbs.Execute strSql, dbfailonerror

    This will sometime give you a more meaningful error message.

  4. #19
    ULMA is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Feb 2015
    Posts
    14
    The Debug.Print strSql does not give any result; this is why I used MsgBox; the dbFailonError did not change the error message " run-time error 3134 Syntax Error in Insert Into Statement.
    I changed the last line to what rpeare suggested but it did not do the trick.
    Any other suggestion?

  5. #20
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    This should not matter, but you never know:

    Add a blank after eventlog in :

    strSql = "INSERT INTO eventlog "

  6. #21
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I found it.

    It is your use of "Date" as a field name - I was able to replicate the error by doing just that.

    Change the field name in your table and your code to EventDate (for example), and everything should work.

  7. #22
    ULMA is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Feb 2015
    Posts
    14
    I would love to say it worked but it didn't. I am so embarrassed to take so much of your valuable time but I really ran out of resources here. for your help and if you have any other suggestion, please let me know.

  8. #23
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    What is the latest version of your code and the resulting SQL statement?

  9. #24
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You said order is a number type but you have apostrophe delimiters for its parameter. Remove them.

    Ooops. Apostrophes show in post 11 but not post 15. Wow, this is so weird.

    If you must use date as field name, enclose it in [] in the SQL statement.


    If you want to provide db for analysis, follow instructions at bottom of my post.
    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. #25
    ULMA is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Feb 2015
    Posts
    14
    The field Order is filled by strSql = strSql & Me!ReturnNo & "," Is this the correct way to express it?

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

    My SQL statement is now:
    Dim dbs As DAO.Database, strSql As String
    Set dbs = CurrentDb
    strSql = ""
    strSql = "INSERT INTO eventlog "
    strSql = strSql & "(eventdate, 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
    MsgBox strSql
    dbs.Execute strSql, dbFailOnError

  12. #27
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Ah, you have seen what the rest of us missed!

    No, it isn't right because it results in a missing blank before the number. Try this:

    strSql = strSql & " " & Me!ReturnNo & ","

    to add a blank.

  13. #28
    ULMA is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Feb 2015
    Posts
    14
    Sorry John, it did not change anything. Are you implying that I need a blank before each variable? If true this might be the problem.

  14. #29
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    No, you don't need the blanks in the value list - the commas are the delimiters.

    Post the code and the resulting SQL again and I'll have a look later. I have a hunch we're missing something obvious!

  15. #30
    ULMA is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Feb 2015
    Posts
    14
    INSERT INTO eventlog (eventdate, accountno, approvalcode, order, sku, price, discount, code, event) Values(#2/24/2015 4:06:46 PM#, '113A230', '45678',16722, '200-7-9341',0,0,0,'Non Returnable Item');

    Dim dbs As DAO.Database, strSql As String
    Set dbs = CurrentDb
    strSql = ""
    strSql = "INSERT INTO EventLog "
    strSql = strSql & "(eventdate, 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!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, dbFailOnError

    Thank you.

Page 2 of 3 FirstFirst 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