Access 2007 VBA: Find and Replace a Character in a Table

[brownrek]

I have spent several hours trying to write the code to search for an apostrophe (') in a specific field in a table and have it replaced by a null value. The code could either be on this specific field or the entire table. Usually the data generated would be less than 100 records. I can get the DoCmd.RunCommand acCmdReplace pop up to work, but want the process to be seamless without manual intervention. A query updates the data into a temporary table [tblTempTestingInsert subform] and the field name within this table to be searched is [PLANNAME]. I'd appreciate any help that could be provided.

 

[genomon]

How about DoCmd.RunSQL "UPDATE yourtable SET yourfield = REPLACE(yourfield, "'", "")" ?

Gluais faicilleach le cupan làn

 

[brownrek]

Got a Run-time error '3075':

Syntax error (missing operator) in query expression 'REPLACE(PLANNAME,'.

 

[genomon]

Please post back the code that you modified.

Gluais faicilleach le cupan làn

 

[Andrzejek]

How about:

UPDATE [tblTempTestingInsert subform]
SET [PLANNAME] = NULL
WHERE InStr([PLANNAME], "'") > 0

But just to be clear:

search for an apostrophe (') in a specific field in a table and have it replaced by a null value

The code will replace teh whole field that has ' with NULL

Have fun.

---- Andy

 

[genomon]

My bad - the search field in the REPLACE function has to be a string, not a field name. The function would work on an open recordset if you used rst!fieldname.
I'd go with Andrzejek's solution.

Gluais faicilleach le cupan làn

 

[brownrek]

Please bear with me, I'm new at these online helps. I apologize if I did not specify in a more direct way exactly what I am trying to do. I need to replace ONLY the apostrophe with a null value. For example: [PLANNAME] "Tony's Pizza" needs to read "Tonys Pizza". I am trying to automate a SQL script generator. The extra apostrophe in "Tony's Pizza" screws up the script.

 

[dhookom]

There are specific versions/service packs of Access where Replace() is not available in a query. Forget about using "NULL".

Try:

UPDATE [tblTempTestingInsert subform]
SET [PLANNAME] = Replace(PLANNAME,"'","")
WHERE InStr([PLANNAME], "'") > 0

or (caution, air code)

Dim strSQL as String
strSQL = "UPDATE [tblTempTestingInsert subform] " & _
  "SET [PLANNAME] = Replace(PLANNAME,""'"","""") " & _
  "WHERE InStr([PLANNAME], ""'"") > 0 "
CurrentDb.Execute strSQL, dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

If the name is "Tony's Pizza" then you should have "Tony's Pizza". By the same token, if your name is O'Brien you need to display it as O'Brien, and not OBrien. Right?

"The extra apostrophe in "Tony's Pizza" screws up the script."

I agree.

What you need to do - in my opinion - is to replace a single apostrophe with 2 single apostrophes (NOT the double quote) and in your database you will keep [tt]"Tony''s Pizza"[/tt] - which will display as "Tony's Pizza"

You can fix your table by:

Dim strSQL as String

strSQL = "UPDATE [tblTempTestingInsert subform] " & _
  "SET [PLANNAME] = Replace(PLANNAME,""[red]'[/red]"",""[red]''[/red]"") " & _
  "WHERE InStr([PLANNAME], ""'"") > 0 "

CurrentDb.Execute strSQL, dbFailOnError

And you need to modify your code in any place where the name is added or updated to replace any single quote with 2 single quotes.

Have fun.

---- Andy

 

[brownrek]

Consider this item closed. Thanks dhookom and Andrzejek! I had to make one minor adjustment to my code, but got it to work using the above. Both of you take out $50 from petty cash!