homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Search & replace text in MS Access
whats up skip

10+ Year Member

Msg#: 31 posted 9:56 pm on Sep 13, 2005 (gmt 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.



WebmasterWorld Senior Member 10+ Year Member

Msg#: 31 posted 5:36 am on Sep 15, 2005 (gmt 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)

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved