Run-Time Error with SQL Update in VBA

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • 315hughes
    New Member
    • Nov 2011
    • 44

    Run-Time Error with SQL Update in VBA

    *Edit: Split from thread http://bytes.com/topic/access/answer...query-into-vba*

    Thank you very much as well TheSmileyCoder i have followed you instructions to keep it all to best practice.
    IOne more question if its ok, I have updated the value of x to be an Integer, this works ok but i have put a update sql into the vba to update the table based on the value of x. All the data in the table is a number/integer but i get a error saying. "Run-time error '3464' Data type mismatch in criteria expression" the sql statement is
    Code:
    db.Execute "UPDATE Location SET Location.ID = 0 WHERE Location.ID =' & x & '"
    If i change the value of x to 1 it works fine so why wont it work with the value from the vba/sql result?
    Thanks
    Last edited by Stewart Ross; Jul 12 '12, 07:23 PM. Reason: Explained split
  • zmbd
    Recognized Expert Moderator Expert
    • Mar 2012
    • 5501

    #2
    If X is type integer now then you should remove the quotes from around the X... otherwise SQL thinks it's a string.

    Personal choice... I tend to build my SQL string first then call the execute. This way I can do a debug.print sSQL incase I need to check how it's formed.
    Code:
    Dim iX as integer
    Dim sSQL as string
    sSQL= "UPDATE Location SET Location.ID = 0 WHERE Location.ID = " & iX
    db.Execute sSQL
    -z

    Comment

    • 315hughes
      New Member
      • Nov 2011
      • 44

      #3
      I presume the " should go at the end of the SQL update statement? and not like
      Code:
      Location.ID = " & x
      because that would mean the x is not part of the SQL. Also even after removing the quotes it still wont work i have tried it both ways if i do it this way
      Code:
      db.Execute "UPDATE Location SET Location.ID = 0 WHERE Location.ID = & x"
      i get a error saying "syntax error (missing operator) in query expression 'Location.ID = & x'.
      if i do it like
      Code:
      db.Execute "UPDATE Location SET Location.ID = 0 WHERE Location.ID =" & x
      it wont give an error but wont work because it looks like the value of x is not part of the SQL
      Thanks for any reply in advance

      Comment

      • zmbd
        Recognized Expert Moderator Expert
        • Mar 2012
        • 5501

        #4
        The SQL string stands as written,

        As you haven't started your new post yet:

        No, the example is as is... your iX has the integer value assigned to it from earlier in your code.

        The following is an example taken from part of a code I use to update a particular record in an actual database that I use in production to track 100's of containers:

        The code is in a form (frm_qaqc_updat estate) which is opened from another form that deals with the actual container information (note that is is only a few lines out of many as there is validation and signiture and on and on and on involved with updating a container's status).

        I've placed the subparts of the string on seperate lines so you can see how they relate:
        Code:
        zsqlstring = 
        "UPDATE
        tbl_tmp_qaqchold
        SET
        qaqcstat = '" & z_str_cbobx_qaqcstat & "' 
        WHERE 
        ContainerRecord_id_pk = " & z_int_addtolist
        zdb.Execute zsqlstring, dbFailOnError
        now normally zsqlstring would be all on one line.

        On line 5: "qaqcstat" is text field in the tbl_tmp_qaqchol d, that will be set to a string value taken from a form control - notice the single quotes " ' " - if it were a date then the " ' " would be replaced by a pound sign "#" an integer would be as on....

        .... On line 7: ContainerRecord _id_pk is a field in tbl_tmp_qaqchol d, it is a long integer (actually an autonumber field used to track the containers but that's another story). Notice how z_int_addtolist just hangs there? z_int_addtolist was set earlier in the form to be the integer id for the container.

        SO let's say the lab is updateing the qaqcstatus for container 23 to hold:
        z_int_addtolist = 23
        z_str_cbobx_qaq cstat = hold

        so if you were to insert DEBUG.PRINT zsqlstring between lines 7 and 8 then in the immedate window you would get:
        Code:
        UPDATE tbl_tmp_qaqchold SET qaqcstat = 'Hold' WHERE ContainerRecord_id_pk = 23
        There you have it.
        -z

        Comment

        • 315hughes
          New Member
          • Nov 2011
          • 44

          #5
          ZMBD I must say a very big thank you for all of your help. I never did post the new post as it was a school boy error by myself by using the wrong values in the SQL statement. The last comment is very detailed and very informative thanks. But i do have an issue how to i update two values in a SQL update the code is below
          Code:
          DoCmd.RunSQL "UPDATE [TableToUpdate]SET [ProdID]=800 , [TimeStamp]=#" & Format(Date, "yyyy-mm-dd") & "# WHERE [ProdID] = 0 AND [ID]=(SELECT MIN(ID)AS low FROM [TableToUpdate] WHERE [ProdID] = 0);"
          I took notice of you above post and the date works fine but what i am trying to change is where the value is 800 for the ProdID update this needs to be the Int value of L which is declared further up in my code. I have tried
          Code:
          DoCmd.RunSQL "UPDATE [TableToUpdate]SET [ProdID]=" & L , [TimeStamp]=#" & Format(Date, "yyyy-mm-dd") & "# WHERE [ProdID] = 0 AND [ID]=(SELECT MIN(ID)AS low FROM [TableToUpdate] WHERE [ProdID] = 0);"
          at it gives my an error which highlights the first # tag from the code and says compile error expected expresion. No doubt this is one character im missing any help would be much apprcieated.

          Comment

          • zmbd
            Recognized Expert Moderator Expert
            • Mar 2012
            • 5501

            #6
            say.. just to let you know I did see this; however, I'm on my way "out-the-door" so to speak... I may not get a chance to take a careful look until a little later tonight or tomorrow.

            If someone else can help before I can take a look, please feel free to jump-in the water... it's nice and cool! (it's like 95°F outside... looking forward to the pool at home!)

            With a VERY quick look at your second code; try the following:
            Code:
            sSQL ="UPDATE [TableToUpdate]SET [ProdID]=" & L , [TimeStamp]= "#" & Format(Date, "yyyy-mm-dd") & "#" WHERE [ProdID]  = 0 AND [ID]=(SELECT MIN(ID) AS low FROM [TableToUpdate] WHERE [ProdID] = 0);" 
            DoCmd.RunSQL sSQL
            Looks like there were a few missing quote marks and a missing space or two... SQL engines are very touchy about such things in the parsing.




            -z
            Last edited by zmbd; Jul 9 '12, 10:51 PM. Reason: added quick solution

            Comment

            • 315hughes
              New Member
              • Nov 2011
              • 44

              #7
              i sure wish i was where ever you where as always wet and windy in the wonderful UK. i copied and pasted your code and i get the error compile error, Expected, end of statement by the , after the L
              the code is as follows
              Code:
              sSQL = "UPDATE [TableToUpdate]SET [ProdID]=" & L , [TimeStamp]= "#" & Format(Date, "yyyy-mm-dd") & "#" WHERE [ProdID]  = 0 AND [ID]=(SELECT MIN(ID) AS low FROM [TableToUpdate] WHERE [ProdID] = 0);"

              Comment

              • zmbd
                Recognized Expert Moderator Expert
                • Mar 2012
                • 5501

                #8
                Looks like there is a space between "L" and the comma and a missing "(" As I said, those SQL engines are very much the stickler for their p's and q's!

                Sorry, feeling well under the weather today and somewhat yesterday too... please try:

                Code:
                sSQL = "UPDATE [TableToUpdate] SET [ProdID]=" & L & ", [TimeStamp]=#" & Format(Date, "yyyy-mm-dd") & "# WHERE [ProdID] = 0 AND [ID]= (SELECT MIN(ID) AS low FROM [TableToUpdate] WHERE [ProdID]= 0);"

                Comment

                • 315hughes
                  New Member
                  • Nov 2011
                  • 44

                  #9
                  :D Thank you every so much for the help, and you really souldnt appologise your helping me out. That worked a treat you deserve a well earned rest. Thanks again
                  R

                  Comment

                  • zmbd
                    Recognized Expert Moderator Expert
                    • Mar 2012
                    • 5501

                    #10
                    I'm glad I could help...
                    -z

                    [EDIT] 2012-07-11:2125CST - asked mods to split this thread starting at post 5 per OP's post in #8 - Z[/EDIT]

                    Comment

                    Working...