Removing ""

  • Thread starter Thread starter Thrava
  • Start date Start date
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
 
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
 
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.
 
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...
 
In this particular case, you might try.

UPDATE ProductsTable
SET ProductNumber = Mid(ProductNumber,2,Len(ProductNumber)-2)
WHERE ProductNumber Like '"*"'
 
Good call, John. You're right: that's probably going to be more efficient
than using the Replace function.
 
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...
 
Back
Top