By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,122 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,122 IT Pros & Developers. It's quick & easy.

Run-Time Error with SQL Update in VBA

P: 44
*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
Expand|Select|Wrap|Line Numbers
  1. 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
Jul 6 '12 #1
Share this Question
Share on Google+
9 Replies


zmbd
Expert Mod 5K+
P: 5,397
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.
Expand|Select|Wrap|Line Numbers
  1. Dim iX as integer
  2. Dim sSQL as string
  3. sSQL= "UPDATE Location SET Location.ID = 0 WHERE Location.ID = " & iX
  4. db.Execute sSQL
-z
Jul 6 '12 #2

P: 44
I presume the " should go at the end of the SQL update statement? and not like
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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
Jul 6 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
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_updatestate) 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:
Expand|Select|Wrap|Line Numbers
  1. zsqlstring = 
  2. "UPDATE
  3. tbl_tmp_qaqchold
  4. SET
  5. qaqcstat = '" & z_str_cbobx_qaqcstat & "' 
  6. WHERE 
  7. ContainerRecord_id_pk = " & z_int_addtolist
  8. zdb.Execute zsqlstring, dbFailOnError
now normally zsqlstring would be all on one line.

On line 5: "qaqcstat" is text field in the tbl_tmp_qaqchold, 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_qaqchold, 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_qaqcstat = hold

so if you were to insert DEBUG.PRINT zsqlstring between lines 7 and 8 then in the immedate window you would get:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tbl_tmp_qaqchold SET qaqcstat = 'Hold' WHERE ContainerRecord_id_pk = 23
There you have it.
-z
Jul 7 '12 #4

P: 44
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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 9 '12 #5

zmbd
Expert Mod 5K+
P: 5,397
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 95F outside... looking forward to the pool at home!)

With a VERY quick look at your second code; try the following:
Expand|Select|Wrap|Line Numbers
  1. 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);" 
  2. 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
Jul 9 '12 #6

P: 44
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
Expand|Select|Wrap|Line Numbers
  1. 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);"
Jul 9 '12 #7

zmbd
Expert Mod 5K+
P: 5,397
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:

Expand|Select|Wrap|Line Numbers
  1. 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);"
Jul 10 '12 #8

P: 44
: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
Jul 10 '12 #9

zmbd
Expert Mod 5K+
P: 5,397
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]
Jul 10 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.