Run-Time Error with SQL Update in VBA

[315hughes]

*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

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

[zmbd]

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.

Dim iX as integer
Dim sSQL as string
sSQL= "UPDATE Location SET Location.ID = 0 WHERE Location.ID = " & iX
db.Execute sSQL

-z

[315hughes]

I presume the " should go at the end of the SQL update statement? and not like

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

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

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

[zmbd]

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:

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_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:

UPDATE tbl_tmp_qaqchold SET qaqcstat = 'Hold' WHERE ContainerRecord_id_pk = 23

There you have it.
-z

[315hughes]

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

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

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.

[zmbd]

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:

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

[315hughes]

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

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);"

[zmbd]

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:

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);"

[315hughes]

: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

[zmbd]

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]