Update Query

[DavidDF]

I have an Access 2003 database of about 17,000 records. An online service is cleaning it up by matching with USPS Change of Address forms & taging Deceased entries. No data is actually changed. New info is added to end of each record.

I want to compare the old data file with new data file. Both have same auto-number ID field. I want to do something like this in an Update Query:

If new_street is not empty replace street with new_street,
if new_city is not empty replace city with new_city.

Any help will be appreciated. Thanks.

David

 

[formerTexan]

Hi David,

Running a variation in VBA on the following should work. Set "SomeVariable" to be the new (replacing) data for new_street.

UPDATE YourTableName SET YourTableName.new_street = '" & SomeVarable & "'
WHERE ((Len("new_street")>0));