Dear,

I'm looking for some sort of "find and replace" in order to look for a part of text in a field from one table and replace that part of text by an other field from another table. I believe this can be done via an update query... I tried (see below) but I didn't manage to get the right solution.

Situation:
2 tables: [customers] , [products]

table [customers] has a memo field containing information about the handling of products per customer (e.g.: for product XXX we need to attach a blue label)
table [products] contains the mapping of the old product numbers with the new product nrs. (only 2 columns) - Important remark: some old product names are mapped with 1 new name ... and vice versa (multiple old with 1 new is not that big of problem, but 2 new for 1 old could be a problem!)
Goal is to find the old product references in the memo field and replace (only) those old product names with the new ones. (so replace the XXX in previous example mapped with the new product YYY)

(note: since the high amount of entries (+750) we don't want to manually find and replace it CTRL+F)

Using the update query - tryout:
UPDATE Customers, Products SET Customers.[Cust_txt] = [Products].[Old_prdct]
WHERE (((Customers.[Cust_txt]) Like "*" & [Products].[Old_prdct] & "*"));

This resulted in finding the part of text in correct field but instead of only replacing the part of text, it replaces the whole content of the field ... :confused:

Can anyone help me out on this please?
Thanks in advance!
Regards,
T

Check out the Replace() function.

http://office.microsoft.com/en-us/access/HA012288981033.aspx