UPDATE Stored Procedure using Replace function

[mrussell71]

I am using stored procedures to update an Access database. The majority of these procedures are DELETE queries and these run flawlessly. However, I am unable to get the right syntax for an UPDATE query.

sSQL = "CREATE PROCEDURE procEPCUpdateNGKPOUND " _
& "AS SELECT tblEPC SET " _
& "tblEPC.PartNo = Replace(tblEPC.PartNo, Chr(35), '') " _
& "WHERE((tblEPC.MfrCode)= 'NGK') "
CreateStoredProc(sSQL)

It doesn't like Replace. Any suggestions as to the right syntax would be greatly appreciated.

 

[chiph]

Replace is a VB function, not a SQL function.

Move your double-quotes to have VB evaluate it:

& "tblEPC.PartNo = '" & Replace(tblEPC.PartNo, Chr(35), '') & "' " _

If your PartNo field in the database is a nchar or nvarchar datatype you should also put an "N" in front of the single-quote to make sure that SQL understands that it's a Unicode value:

& "tblEPC.PartNo = N'" & Replace(tblEPC.PartNo, Chr(35), '') & "' " _

You can also avoid using the Replace function altogether by going through ADO parameters - they will take care of replacing not only the single-quotes, but double-quotes too (they also cause problems for the database).

Chip H.