Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Search & replace text in MS Access

9:56 pm on Sep 13, 2005 (gmt 0)

Preferred Member

10+ Year Member

joined:June 20, 2002
votes: 0

I am trying to find and replace many different text variables within one text field in a MS Access data base.

I do have a table of variables and what I want to replace them with, however I cannot workout how to automate this process. My knowledge of VB, and SQL is very poor.

I can get this simple command to work, but I cannot get it work through the whole table of variables that need to be replaced. The issue here is that variable only makes up part of the text in the field "HotelName".

UPDATE HotelData SET HotelData.HotelName = Replace([HotelData]![HotelName],"Melbourne","Melb");

I tried to added to this but had no luck. Such as

UPDATE HotelData SET HotelData.HotelName = Replace([HotelData]![HotelName],"Melbourne","Melb");UPDATE HotelData SET HotelData.HotelName = Replace([HotelData]![HotelName],"Sydney","Syd");

This obviously creates an error, but I could not workout the correct structure.

As you can see a bit lost at sea with this.

5:36 am on Sept 15, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 13, 2002
votes: 0

The query you posted can be re-written as:

Update HotelData SET HotelName = IIF(HotelName = "Syndney,"Syd","Melb")

You need to add more iff statements for each replaces, but its probably as easy to write sepearte updates.

You might get away with this for some though:
UPdate HotelData SET HotelName = Left(HotelName,4)