REPLACE function

srbooth · Oct 23, 2007

I am trying to use an update query to modify some text in a specific field using the REPLACE function without much luck!!!

I have a field called "Option" and I want to replace the word "Metallic" with "Paint" and the word "Electric" with "Windows".

Could someone please point me in the right direction.

Thanks in anticipation.

neileg · Oct 23, 2007

Can you post the SQL of your query?

CraigDolphin · Oct 23, 2007

First, backup your table in case you need to start over.

Then, run an update query that first populates your field with Replace([Option],"Metallic","Paint") in the 'Update To' part of the design grid for your field.

ie

Code:

UPDATE YourTableName SET YourTableName.Option = Replace([Option],"Metallic","Paint");

Then, re run the query but change the expression to:
Replace([Option],"Electric","Windows")

srbooth · Oct 23, 2007

This is the SQL code

UPDATE tblSpecOptions SET tblSpecOptions.[Option] = Replace([Option],"Electric","Windows");

but I keep getting a compile error!!!

srbooth · Oct 23, 2007

Does Access 2000 support the Replace function?

CraigDolphin · Oct 23, 2007

Yes, Access 2000 is what I use. The SQL looks fine to me.
There's a page here which may explain the issue (http://www.tech-archive.net/Archive/Access/microsoft.public.access.queries/2004-03/0774.html)

Some things to check:

Look for missing references in the VBA editor's Tools menu.
Compile your db via the VBA editor's Debug menu and fix any errors you come across.
Make sure you haven't created a custom function called 'Replace' that might be interfering with the built-in function.
Make sure none of your field names are found in the list of reserved words for Access (http://support.microsoft.com/kb/209187).
Make sure your version of jet is up to date.

If all that is checked and ok, try creating a new db object and import all the objects from your exisitng db into it. Compact and repair and try again.

If that fails, maybe you need to decompile/recompile your db.

EMP · Oct 23, 2007

Not sure if this helps ... but I recall reading somewhere that some versions of Access require the creation of a wrapper function in a Module for the replace function e.g.

Code:

Public Function MyReplace(Fld, ReplThis, WithThis) As String
    MyReplace = Replace(Fld, ReplThis, WithThis)
End Function

If the wrapper function works, you can nest it in an update query like this:-

UPDATE [TableName] SET [TableName].[Option] = MyReplace(MyReplace([Option],"Metallic","Paint"),"Electric","Windows");

^

boblarson · Oct 23, 2007

Well, it would look like the column named OPTION is using an Access reserved word. OPTION is a reserved word. I think you might want to change the column name to something else.

REPLACE function

srbooth

Registered User.

neileg

AWF VIP

CraigDolphin

GrumpyOldMan in Training

srbooth

Registered User.

srbooth

Registered User.

CraigDolphin

GrumpyOldMan in Training

EMP

Registered User.

boblarson

Smeghead

Similar threads

Users who are viewing this thread