Remove specific word

[srbooth]

Hoping someone can help.

I want to remove the word "Metallic" and the word "Pearl" from a field called "Options" in a table called "Specification" and be left with everything else.

Thanks

 

[WayneRyan]

sr,

Update Specification
Set    [Options] = Replace(Replace([Options], "Metallic", ""), "Pearl", "")

Wayne

 

[pbaldy]

You could use the Replace function in an UPDATE query. More info in Help.

 

[srbooth]

Error

Thanks for that but when I run it I get

A Compile Error:

Invaild outside procedure

 

[WayneRyan]

sr,

Don't paste that code in the code window!

You can paste it into the "SQL View" of a query, then run and save it.
It WILL change your data though!

Or you can use the Query Design grid and past the Replace part in the
update to part of the grid. It will also change your data!

Wayne

 

[srbooth]

What would the code be for that?

 

[WayneRyan]

sr,

Make a new query for your specification table.

In Design View, Right-Click and select --> SQL View

Then past in:

Update Specification
Set    [Options] = Replace(Replace([Options], "Metallic", ""), "Pearl", "")

But, BACKUP your data first.

Wayne

 

[CraigDolphin]

This appears to be a re-post of this thread (http://www.access-programmers.co.uk/forums/showthread.php?t=137527). There is apparently a flaw in some of the early versions of jet that originally shipped with Access 2000 that causes a compile error when the replace function is used in an update query.

Did you try any of the suggestions put to you in that thread, srBooth?

You could also just open your table, select the relevant field, and press ctrl-f. type in the text you want to find, select the replace tab, and type in the replacement text you want for that word (make sure the search is for anywhere in the field). Then hit replace all. It won't fix the problem with your version of access but it would fix this issue if it's a one-off situation.