SQL Update

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • MarkBStewart
    New Member
    • May 2010
    • 20

    SQL Update

    I am attempting to update a table in Access/VBA using SQL. Here is the statement:

    MySQL = "UPDATE ParsingTable SET ParsingTable.[Tier1] = intTier1, ParsingTable.[Tier2] = intTier2, ParsingTable.[Tier3] = intTier3, ParsingTable.[Tier4] = intTier4, ParsingTable.[Tier5] = intTier5 WHERE (((ParsingTable .doc_label) = strReference)); "
    DoCmd.SetWarnin gs False
    DoCmd.RunSQL MySQL
    DoCmd.SetWarnin gs True

    When I execute the SQL Update it asks me for "data input" for each of the variables intTier1 through intTier2 as well as strReference. All of the intTier1-5 variables are Dim as Integer and have values in them. strReference is DIM as String and has a value in it.

    Why is the module asking for input values for all of my variables?

    Thx

    Mark
  • vb5prgrmr
    Recognized Expert Contributor
    • Oct 2009
    • 305

    #2
    Code:
    MySQL = "UPDATE ParsingTable SET ParsingTable.[Tier1] =" &  intTier1 & ", ParsingTable.[Tier2] =" & intTier2 & ", ParsingTable.[Tier3] = " & intTier3 & ", ParsingTable.[Tier4] = " & intTier4 & ", ParsingTable.[Tier5] =" & intTier5 & "  WHERE (((ParsingTable.doc_label) = strReference));"


    Good Luck

    Comment

    • MarkBStewart
      New Member
      • May 2010
      • 20

      #3
      Originally posted by vb5prgrmr
      Code:
      MySQL = "UPDATE ParsingTable SET ParsingTable.[Tier1] =" &  intTier1 & ", ParsingTable.[Tier2] =" & intTier2 & ", ParsingTable.[Tier3] = " & intTier3 & ", ParsingTable.[Tier4] = " & intTier4 & ", ParsingTable.[Tier5] =" & intTier5 & "  WHERE (((ParsingTable.doc_label) = strReference));"


      Good Luck
      First, thank you for the help.

      Second, for the variable strReference am I to assume that it also requires "& strReference &"?

      When I left it as in your example I still recieved a prompt for input. When I used the "& strReference &" format I am receiving a error indicating I have a problem with '.' '!' or '()". Not sure where to go from here. Can you help?

      Thx

      Mark

      Comment

      • dsatino
        Contributor
        • May 2010
        • 393

        #4
        what does strReference actual equal?

        Comment

        • MarkBStewart
          New Member
          • May 2010
          • 20

          #5
          strReference = A.1.1.3 (for example).

          However, I did the following and it worked:

          MySQL = "UPDATE ParsingTable SET ParsingTable.Ti er1 = " & intTier1 & ", ParsingTable.Ti er2 = " & intTier2 & ", ParsingTable.Ti er3 = " & intTier3 & ", ParsingTable.Ti er4 = " & intTier4 & ", ParsingTable.Ti er5 = " & intTier5 & " WHERE ParsingTable.do c_label = '" & strReference & "';"

          It seems that because strReference is a text string wrapping " & strReference & " within ' ' is required. At any rate ti worked.

          Thanks for looking at this.

          Mark

          Comment

          • dsatino
            Contributor
            • May 2010
            • 393

            #6
            Yes, the ' ' are required, but that has to do with the conversion to SQL. If you were only use the variable within VBA you'd be okay without them.

            Comment

            • MarkBStewart
              New Member
              • May 2010
              • 20

              #7
              Originally posted by dsatino
              Yes, the ' ' are required, but that has to do with the conversion to SQL. If you were only use the variable within VBA you'd be okay without them.
              Thanks again. I am off and running.

              Mark

              Comment

              Working...