Getting rid of " in a field in a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am importing Excel data into an Access table. When the teacher name text field is imported into Access, it puts "" at the beginning and end of the teacher's name. I have other text fields being imported & it doesn't do that to those fields; only the teacher name field. I want to write a macro that would clean up that field so that the "" will be deleted. Or I want to use the Find/Replace feature. However, when I go to find/replace I don't know how to tell it to just delete it rather than try to replace it with something

Any ideas?
 
Replace it with nothing. Put " in the Find, and nothing in the Replace

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bogga said:
I am importing Excel data into an Access table. When the teacher name
Click to expand...
text field is imported into Access, it puts "" at the beginning and end of
the teacher's name. I have other text fields being imported & it doesn't do
that to those fields; only the teacher name field. I want to write a macro
that would clean up that field so that the "" will be deleted. Or I want to
use the Find/Replace feature. However, when I go to find/replace I don't
know how to tell it to just delete it rather than try to replace it with
something.
 
When I type " in the Find what field & leave the Replace field blank, it tells me it can't find the character I am looking to replace. If I just do a find next without using the replace field, it finds the " character
 
I am importing Excel data into an Access table. When the teacher name text field is imported into Access, it puts "" at the beginning and end of the teacher's name. I have other text fields being imported & it doesn't do that to those fields; only the teacher name field. I want to write a macro that would clean up that field so that the "" will be deleted. Or I want to use the Find/Replace feature. However, when I go to find/replace I don't know how to tell it to just delete it rather than try to replace it with something.

Any ideas?
Click to expand...

Sorry for the belated answer - just found this.

An Update query will do the trick here. If you have Access2002 or
2003, you can update the field to

Replace([fieldname], Chr(34), "")

to replace all instances of the doublequote character (ASCII code 34)
with an empty string.
 
Back
Top