Removing ""

T

Thrava

Hi group.
one of my tables has a field with product number that is
flanked with double quotes. The length of the product
number can vary from 3 to 8. Eg. "N123" or "M0021111".

I want to use an update query and remove the double quotes
from these fields.

Can anyone help with what what I should type in the Update
to: cell of update query please?

Thanks
 
D

Douglas J. Steele

If you're using Access 2000 or newer, you should be able to use the Replace
function in your Update query.

In the Update to cell, put Replace([FieldName], Chr$(34), "")

(replace FieldName with the appropriate field name)

NOTE: In some versions of Access 2000, the Replace function won't work in
queries. To get around this, you need to create a wrapper function that
calls the Replace function, and use that in your query instead. The wrapper
function will be something like:

Function MyReplace(Value As Variant, ChangeFrom As String, ChangeTo As
String) As Variant

MyReplace = Replace(Value, ChangeFrom, ChangeTo)

End Function
 
V

Van T. Dinh

If you use A2K2 or later:

UPDATE [YourTable]
SET [ProdCode] = Replace([ProdCode], """", "")

For A97, there is no inbuilt Replace() function but you can find a custom
Replace() on the Web. For A2K0, theReplace() function exists but there is a
bug that prevents it to work in Access Queries and you need to write a
simple wrapper function.
 
K

Ken Snell [MVP]

Jet SP6 and higher for version 4 fixes that Replace bug in 2000, I believe.

--

Ken Snell
<MS ACCESS MVP>

Van T. Dinh said:
If you use A2K2 or later:

UPDATE [YourTable]
SET [ProdCode] = Replace([ProdCode], """", "")

For A97, there is no inbuilt Replace() function but you can find a custom
Replace() on the Web. For A2K0, theReplace() function exists but there is a
bug that prevents it to work in Access Queries and you need to write a
simple wrapper function.

--
HTH
Van T. Dinh
MVP (Access)


Thrava said:
Hi group.
one of my tables has a field with product number that is
flanked with double quotes. The length of the product
number can vary from 3 to 8. Eg. "N123" or "M0021111".

I want to use an update query and remove the double quotes
from these fields.

Can anyone help with what what I should type in the Update
to: cell of update query please?

Thanks
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

In this particular case, you might try.

UPDATE ProductsTable
SET ProductNumber = Mid(ProductNumber,2,Len(ProductNumber)-2)
WHERE ProductNumber Like '"*"'
 
D

Douglas J. Steele

Good call, John. You're right: that's probably going to be more efficient
than using the Replace function.
 
T

Thrava

Thank you for your help
-----Original Message-----
If you're using Access 2000 or newer, you should be able to use the Replace
function in your Update query.

In the Update to cell, put Replace([FieldName], Chr$(34), "")

(replace FieldName with the appropriate field name)

NOTE: In some versions of Access 2000, the Replace function won't work in
queries. To get around this, you need to create a wrapper function that
calls the Replace function, and use that in your query instead. The wrapper
function will be something like:

Function MyReplace(Value As Variant, ChangeFrom As String, ChangeTo As
String) As Variant

MyReplace = Replace(Value, ChangeFrom, ChangeTo)

End Function



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi group.
one of my tables has a field with product number that is
flanked with double quotes. The length of the product
number can vary from 3 to 8. Eg. "N123" or "M0021111".

I want to use an update query and remove the double quotes
from these fields.

Can anyone help with what what I should type in the Update
to: cell of update query please?

Thanks
Click to expand...


.
Click to expand...
 
Top