Update field with contents of another field

[JohnB]

I now know how to update a field, with text such as "record found", based on
the contents of another field.

I use something like this:
update tableb,tablea set tableb.blankfield = "found"
where tableb.stocknumber = tablea.stocknumber

But I need to do something a little different, and I can't figure out how to
do this:

I have a file with a link to a table in another file. And just as before, I
need to read the StockNumber field in one table and find matching records in
the linked table. When I find a match I need to update the contents of
Table-B.Location with the contents of Table-A.location

I tried, in place of "found", TableName.FieldName. but I get a message
saying Syntax error, missing operator.....
It's probably something simple, but since I've never done this before, I
don't know what the correct syntax would be.

TIA

 

[Michel Walsh]

UPDATE tableB INNER JOIN tableA
ON tableB.stockNumber = tableA.stockNumber
SET tableB.Location= tableA.Location
WHERE tableB.Location IS NULL


Sure, just in case it does not do what you want, preferable to experiment
AFTER having made a backup. An UPDATE can make extensive damage to your
data, if not properly done.

Vanderghast, Access MVP