Updating a field based on another field

[gbm]

Hello,

I have two tables in access, Table1 & Table2.
Table1 has fields: LocalityCode, StreetName
Table2 : Address, LocalityCode.

I have to update LocalityCode from Table2 with localitycode of table1 if the address field of Table2 contains the streetname of Table1. The streetname can be in any position in the field address.

My problem is which function to use in the SQL statement in order to compare address field with the streetname.

Thanks in advance for the help.

 

[ldandy]

You could use the LIKE operator in the WHERE condition of the update statement (see the help for LIKE). However, if there are streets with the same name in different localities it won't work as you expect (the update is made to the last locality code found).

Danny.