none
Why do I have to use single and double quotes on INSERT and UPDATE statements RRS feed

  • Question

  • While writing some code in VBA for Access 2016, I have to use the single and double quotes for the statements.  Is there a way I can do this without having to use them?  If I do not use them, then a dialog box displays prompting me for the data in the VALUES clause or after the SET fieldname = fields.  The data comes from 2 different recordsets that I am using to load into a different table. 

     DoCmd.RunSQL "INSERT INTO tblVehicle(DlrName, Count, Model_N, Year, Make, Model, Model_Combo, Series_Combo, Engine, " & _
                  "CityMPG, HwyMPG, Transmission, Invc_No_Shipping, Msrp_No_Shipping, Percentage_Add, Dollar_Add, Shipping, Addendum_Pgk_Name, " & _
                  "Addum_Price, Addum_Costs, SIR_Mths_36, SIR_Rate_36, SIR_Mths_48, SIR_Rate_48, SIR_Mths_60, SIR_Rate_60, " & _
                  "SIR_Mths_72, SIR_Rate_72, SIR_Mths_84, SIR_Rate_84) " & _
                  "VALUES ('" & rs1!DlrName & "', '" & rs2!Count & "', '" & rs2!ModelNumber & "', '" & rs2!YearCar & "', " & _
                  "'" & rs2!MakeCar & "', '" & rs2!Model & "', '" & rs2!ModelCombo & "', '" & rs2!Series & "', '" & rs2!CarEngine & "', " & _
                  "'" & rs2!CityMPG & "', '" & rs2!HwyMPG & "', '" & rs2!CarTransmission & "', '" & rs2!InvcNoShipping & "', '" & rs2!MsrpNoShipping & "', " & _
                  "'" & rs1!AdvAddInvc & "', '" & wrkDollar_Add & "', '" & rs2!Shipping & "', '" & rs1!OtdAddNameDesc & "', " & _
                  "'" & rs1!OtdPriceAmt & "', '" & rs1!OtdAddTax & "', '" & rs1!SIR_Mths_36 & "', '" & rs1!SIR_Rate_36 & "', '" & rs1!SIR_Mths_48 & "', '" & rs1!SIR_Rate_48 & "', " & _
                  "'" & rs1!SIR_Mths_60 & "', '" & rs1!SIR_Rate_60 & "', '" & rs1!SIR_Mths_72 & "', '" & rs1!SIR_Rate_72 & "', '" & rs1!SIR_Mths_84 & "', '" & rs1!SIR_Rate_84 & "');"

     DoCmd.RunSQL "UPDATE tblVehicle SET Residual_Percentage = '" & rs1!Residual_Percentage & "', Mny_Fac_Zero = '" & rs1!Mny_Fac_Zero & "' , Mny_Fac_Mny = '" & rs1!Mny_Fac_Mny & "', " & _
                  "Miles_per_Yr = '" & rs1!Miles_per_Yr & "' , Months_Lease = '" & rs1!Months_Lease & "' ,"
                  "WHERE tblVehicle.Model_N = '" & rs2!ModelNumber & "';"

    Saturday, January 27, 2018 2:17 PM

All replies

  • The only way to not use quotes is to instead set up a fixed query object (that will appear in the navigation pane).

    Use the query designer feature and set up your update query and save it with a name.

    Then in vba - you can use Docmd to open that named query.  This will fire it to run.

    It is an entirely different approach - one that I prefer because in terms of trouble shooting that stand alone query now can be triggered all by itself easily.

    Saturday, January 27, 2018 3:04 PM
  • You can create an Append query and an Update query.

    In the Field line of the query design, you would enter a parameter, and in the Parameters dialog you would enter the data types. Save it (in my case: qappCustomers)

    Then you would invoke this query using VBA:
    dim qd as dao.querydef
    set qd = currentdb.querydefs("qappCustomers")
    qd!parCompany = rs1!Company
    qd!parLastName = rs1![Last Name]
    'etc.
    qd.execute dbfailonerror
    set qd=nothing


    -Tom. Microsoft Access MVP

    Saturday, January 27, 2018 5:50 PM
  •  DoCmd.RunSQL "UPDATE tblVehicle SET Residual_Percentage = '" & rs1!Residual_Percentage & "', Mny_Fac_Zero = '" & rs1!Mny_Fac_Zero & "' , Mny_Fac_Mny = '" & rs1!Mny_Fac_Mny & "', " & _
                  "Miles_per_Yr = '" & rs1!Miles_per_Yr & "' , Months_Lease = '" & rs1!Months_Lease & "' ,"
                  "WHERE tblVehicle.Model_N = '" & rs2!ModelNumber & "';"

    Hi ballj_351,

    For these purposes I use a small function As_text, in a general module.

    This function surrounds the input text with single quotes (if necessary doubles any single quote within the text).

    Your update query would then look like:

    DoCmd.RunSQL "UPDATE tblVehicle" _
    " SET Residual_Percentage = As_text(rs1!Residual_Percentage) _
    ", Mny_Fac_Zero = " & As_text(rs1!Mny_Fac_Zero) _
    ", Mny_Fac_Mny = " & As_text(rs1!Mny_Fac_Mny) _ ", Miles_per_Yr = " & As_text(rs1!Miles_per_Yr) _
    ", Months_Lease = " & As_text(rs1!Months_Lease) _ & " WHERE tblVehicle.Model_N = " & As_text(rs2!ModelNumber)


    It is easy to write, and easy to read and understand.

    If the fields are numerical (Miles_per_Yr?, Months_Lease?) then you omit the As_text function.

    If the fields are dates then use an analogues As_date function, that converts the date to ISO notation (yyyy-mm-dd) and surrounds the date with "#".

    Imb.


    • Edited by Imb-hb Saturday, January 27, 2018 7:53 PM
    Saturday, January 27, 2018 7:52 PM
  • If you go to that extent, you may as well use the built-in BuildCriteria method. See help file.

    -Tom. Microsoft Access MVP

    Saturday, January 27, 2018 9:18 PM
  • While writing some code in VBA for Access 2016, I have to use the single and double quotes for the statements.

    So, what's the problem with that?  When building a string expression it needs to be wrapped in quotes characters.  Any values within the expression of text data type must also be wrapped in quotes characters.  The latter can be represented by a single quote character, ', to differentiate them from the delimiters of the expression itself, or a literal double quotes character can be represented by a contiguous pair of double quotes characters, "", which is advisable in the case of  personal names or other values which can contain an apostrophe.  My own name in its original non-anglicized form, Cináed O'Siridean, is a case in point.

    I would advise that you first assign the string expression to a variable of string data type, however, and then use the variable when calling the RunSQL or Execute methods.  For debugging purposes you can then print the value of the variable to  the debug (aka immediate) window and copy and paste it into the query designer in SQL view to test it independently of the code.


    Ken Sheridan, Stafford, England

    Sunday, January 28, 2018 12:16 AM
  • When referring to string inside an SQL statement it must be enclosed in quotes. When building it piece by piece it get unwieldly the longer it gets.

    Also if you don't replace ' with '', and a text happens to have it, an error will occur.

    ex. "... SET Model_N = '" & rs!Model_Number & "'"

    if rs!Model_Number = "toyota's camry"

    it will become "... SET Model_N = 'toyota's camry'", which should be "... SET Model_N = 'toyota''s camry'" 

    What I do is (just to show concept first):

    Dim sql as String
    sql = "UPDATE tblVehicle SET Residual_Percentage='[RES%]', Mny_Fac_Zero='[MFZ]', Mny_Fac_Mny='[MFM]';"
    sql = Replace( sql, "[RES%]", rs( "Residual_Percentage" ) )
    sql = Replace( sql, "[MFZ]", rs( "Mny_Fac_Zero" ) )
    sql = Replace( sql, "[MFM]", rs( "Mny_Fac_Mny" ) )
    DoDmc.RunSQL sql

    I replace first numeric and date columns and a single text field last, just in case it contains, say "hello[RES%]".

    For multiple text fields I put bullets and other unusual characters as the token, ex. ... Residual_Percentage='[©RES%©]', as there is less chance of collision. 

    You only need to quote text based columns, so assuming Residual_Percentage is numeric.

    And to be totally safe:

    Function ABC(str As String) As String
       ' replace ' with ''
       str = Replace(str, "'", "''" )
       ' expand seldom used character
       ' so it can't be treated as token anymore 
       ABC = Replace(str, "ª", "' & 'ª' & '")
    End Function

    Use a very seldom used character that can be displayed in the editor, I used from CharMap the character next to the copyright symbol, just as an example.

    Only text based columns need to be enclosed in quotes, so assuming Residual_Percentage is numeric.

    --- Don't know how to delete this box ---

    Dim sql as String
    sql = "UPDATE tblVehicle SET Residual_Percentage=[RES%], Mny_Fac_Zero='ªMFZª', Mny_Fac_Mny='ªMFMª';"
    sql = Replace( sql, "[RES%]", rs( "Residual_Percentage" ) )
    sql = Replace( sql, "ªMFZª", ABC( rs( "Mny_Fac_Zero" ) ) )
    sql = Replace( sql, "ªMFMª", ABC( rs( "Mny_Fac_Mny" ) ) )
    DoDmc.RunSQL sql
    Tuesday, January 30, 2018 7:08 PM
  • Two suggestions.

    I always add to a “bag of tools” set of routines to a given database. That includes a qu(), and qudate() function.

    So

    Dim strSQL   as string

    Dim strSomeCity as string

    To build a sql string, then I use:

    strSQL = "select * from tblCustomers where City = " & qu(strSomeCity)

    So a “handy” routine that puts quotes around text can really help this process. I have these two routines in all my applications:

    Function qu(s As String) As String

       qu = """" & s & """"
      
    End Function

    Public Function quDate(dt As Date) As String

       ' return formatted date
      
       quDate = "#" & Format(dt, "mm\/dd\/yyyy HH:NN:SS") & "#"

    End Function

    Another suggestion is that if the insert has “many” values (such as your example), then use a reocrdset – it ends to be cleaner, and far less work. And you don’t have any quotes to mess with.

    So your code above becomes this:

      Dim rst      As DAO.Recordset   
      Set rst = CurrentDb.OpenRecordset("tblVehicle")
    
       With rst
    
          .AddNew
    
          !DirName = rs1!DirName
          !Count = rs2!Count
          !Model_N = rs2!ModelNumber
          !Year = rs2!YearCar
          !Make = rs2!MakeCar
          !Model = rs2!Model
          !model_Combo = rs2!ModelCombo
          !Series_Combo = rs2!Series
          !Engine = rs2!CarEngine
          .Update
      End With
     

    Now I left out some of the additional columns, but you can see the above is a rather clean solution – and far more readable in this bonus pot.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada



    Tuesday, January 30, 2018 10:40 PM
  • Can anyone see why I would be getting a SQL Error 3144 - Syntax error in update statement, with this statement:

    DoCmd.RunSQL "UPDATE tblDlrVehicles " & _
                 " SET SIR_Rate_36 = " & Forms!frmDlrMthUpd.SIR_Rate_36 & ", " & _
                 " SIR_Rate_48 = " & Forms!frmDlrMthUpd.SIR_Rate_48 & ", " & _
                 " SIR_Rate_60 = " & Forms!frmDlrMthUpd.SIR_Rate_60 & ", " & _
                 " SIR_Rate_72 = " & Forms!frmDlrMthUpd.SIR_Rate_72 & ", " & _
                 " SIR_Rate_84 = " & Forms!frmDlrMthUpd.SIR_Rate_84 & ", " & _
                 " Residual_Percentage = " & Forms!frmDlrMthUpd.Residual_Percentage & ", " & _
                 " Mny_Fac_Zero = " & Forms!frmDlrMthUpd.Mny_Fac_Zero & ", " & _
                 " Mny_Fac_Mny = " & Forms!frmDlrMthUpd.Mny_Fac_Mny & ", " & _
                 " Miles_per_Yr = " & Forms!frmDlrMthUpd.Miles_per_Yr & ", " & _
                 " Months_Lease = " & Forms!frmDlrMthUpd.Months_Lease & ", " & _
                 " Man_Lse_Pmt_Zero = " & Forms!frmDlrMthUpd.Man_Lse_Pmt_Zero & ", " & _
                 " Man_Lse_Pmt_Mny = " & Forms!frmDlrMthUpd.Man_Lse_Pmt_Mny & ", " & _
                 " Man_Money_Dwn = " & Forms!frmDlrMthUpd.Man_Money_Dwn & ", " & _
                 " Rebate_Prog_1 = '" & Forms!frmDlrMthUpd.Rebate_Prog_1 & "' , " & _
                 " Rebate_Prog_1_Dollar = " & Forms!frmDlrMthUpd.Rebate_Prog_1_Dollar & ", " & _
                 " Rebate_Prog_2 = '" & Forms!frmDlrMthUpd.Rebate_Prog_2 & "' , " & _
                 " Rebate_Prog_2_Dollar = " & Forms!frmDlrMthUpd.Rebate_Prog_2_Dollar & ", " & _
                 " Rebate_Prog_3 = '" & Forms!frmDlrMthUpd.Rebate_Prog_3 & "' , " & _
                 " Rebate_Prog_3_Dollar = " & Forms!frmDlrMthUpd.Rebate_Prog_3_Dollar & ", " & _
                 " Rebate_Prog_4 = '" & Forms!frmDlrMthUpd.Rebate_Prog_4 & "' , " & _
                 " Rebate_Prog_4_Dollar = " & Forms!frmDlrMthUpd.Rebate_Prog_4_Dollar & _
                 " WHERE tblDlrVehicles.DlrName=cboMakeDtl " & _
                 "   AND tblDlrVehicles.YearCar=cboYearCar " & _
                 "   AND tblDlrVehicles.Model=cboModel;"

    It used to work fine and now all of a sudden it is not working.  Thanx in advance.

    Wednesday, January 31, 2018 12:17 PM
  • never mind.  I found it.  I did not have all fields with a default value.  Once I gave all the fields a default value in the table definition, this problem went away.
    Wednesday, January 31, 2018 1:26 PM
  • I like the way this is coded!  Very nice.  How could I use an update statement with a where clause using this type of statement.  There are other columns that I want to update after some data calcs.  How would this be formated to use the where clause and update the same record that I inserted earlier?  That would be wonderful to get.  Thanx in advance.
    Thursday, February 1, 2018 6:29 PM
  • Indeed this is a lot less code.

    The only real issue is are you “adding” a record, or are you updating a record.

    If you just adding records, then of course you don’t need any kind of “where” clause for the target table. (you just use the above example – note the “.AddNew”

    Of course your existing code to fill rs! Etc. would remain un-changed.

    However, to update an existing record, then you just select it, and the rest of the code remains the same.

    So in place of:

    Set rst = CurrentDb.OpenRecordset("tblVehicle")

    Rst.Addnew

    You go:

    Set rst = CurrentDb.OpenRecordset("select * from tblVehicle where VehicleID = " & Vid)

    So you still use “some” sql and strings you have to build, but only to select the record. So in the “air” example above I assumed the id is in some var called Vid to select the row we want to update)

    And then right after above, in place of .AddNew, you use “.edit”

    So you start with:

    .AddNew

    .Update

    or

    .edit

    .Update

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Thursday, February 1, 2018 7:06 PM