Search & replace text in MS Access

[whats up skip]

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.

[aspdaddy]

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)
HTH