Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013
    Posts
    70

    Answered: quotes in VBA sql not right

    Greetings, I need some help with quote marks and the end of the sql statement in code below. I am trying to add a location "not in list" and also to put the string "Fire" in the field "callDrill". I get the error "Missing semicolon ( at end of SQL statement".

    Table structure for tblCallsLocationsLU:
    callLocationID autonumber field
    callLocation text field
    muDept text field
    callDrill text field

    Code:
    Private Sub cboLocation_NotInList(NewData As String, Response As Integer)
        On Error GoTo cboLocation_NotInList_Err
        
        Dim intAnswer As Integer
        Dim strSQL As String
        
        intAnswer = MsgBox("The location " & Chr(34) & NewData & _
            Chr(34) & " is not currently listed." & vbCrLf & _
            "Would you like to add it to the list now?" _
            , vbQuestion + vbYesNo, "CFD Operations")
        
        If intAnswer = vbYes Then
        strSQL = "INSERT INTO tblCallsLocationsLU([callLocation]) " & "VALUES ('" & NewData & "')" _
        & "([callDrill]) VALUES" & "('Fire')"";"
        
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            MsgBox "The new location has been added to the list.", vbInformation, "CFD Operations"
            Response = acDataErrAdded
        Else
            MsgBox "Please choose a location from the list.", vbInformation, "CFD Operations"
            Response = acDataErrContinue
        End If
    
    cboLocation_NotInList_Exit:
        Exit Sub
    
    cboLocation_NotInList_Err:
        MsgBox Err.Description, vbCritical, "Error"
        Resume cboLocation_NotInList_Exit
    
    End Sub
    Many thanks, Scott

  2. Best Answer
    Posted by healdem

    "Your sql is invalid, it should take the form
    Code:
    INSERT INTO mytable (my, column, list) VALUES (1, 'Some Text', #2106/04/12#);"
    If a column is autogenerated / autonumber omit it from the column and values list

    The syntax you are currently using is sort of right to insert multiple rows in the same SQL sentence but you would need to terminate each set of values with a semi colon hence the error message. SQL uses a semi colon as and end of sentence symbol, theoretically all SQL should use it but many if not all SQL engines will run without one if the rest of the SQL is decipherable.

    PS you mayneed to requery the list/combo box if the insert is to populate a drop down list"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Your sql is invalid, it should take the form
    Code:
    INSERT INTO mytable (my, column, list) VALUES (1, 'Some Text', #2106/04/12#);"
    If a column is autogenerated / autonumber omit it from the column and values list

    The syntax you are currently using is sort of right to insert multiple rows in the same SQL sentence but you would need to terminate each set of values with a semi colon hence the error message. SQL uses a semi colon as and end of sentence symbol, theoretically all SQL should use it but many if not all SQL engines will run without one if the rest of the SQL is decipherable.

    PS you mayneed to requery the list/combo box if the insert is to populate a drop down list
    Last edited by healdem; 04-12-16 at 04:52.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just a suggestion
    when you have errors with SQL statements its always a smart call to post the actual SQL as well as the code in your original post. this is especially true with things like INSERT statements, where the error may be in the data not necessarily with the VBA code

    you are part way there already by assiging the value of the SQL to a variable
    I'd rather be riding on the Tiger 800 or the Norton

  5. #4
    Join Date
    Mar 2013
    Posts
    70

    sql quotes

    Good morning, I have changed the sql using your example:

    Code:
    strSQL = "INSERT INTO tblCallsLocationsLU([callLocation],[callDrill]) VALUES ('" & NewData & "', 1);"
    The sql now works perfectly. yeah! Thank you!

    One more question.Do you know of any resources I can use to learn how to use quotes in these statements and the differences between SQL in queries and in VBA? I see somany examples of SQL in VBA that have so many quotes it is hard to keep track of them. I took out a lot here and it works better. But the NewData part has a bunch. I am confused about this.

    Again, thanks for the help and your time.
    Scott

Posting Permissions

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