Welcome to WebmasterWorld Guest from 54.147.10.72

Forum Moderators: open

Message Too Old, No Replies

Search & replace text in MS Access

     

whats up skip

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

10+ Year Member



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

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

WebmasterWorld Senior Member 10+ Year Member



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

 

Featured Threads

Hot Threads This Week

Hot Threads This Month