Criteria in multi table update query

[jenngreff via AccessMonster.com]

I am trying to created an update query that will update all nulls for 2
fields in 1 table when the project # in both tables is equal. So far the sql
view of the query looks like this...

UPDATE tblâ€â€œâ€œâ€œControl INNER JOIN tblProjectNames ON tblâ€â€œâ€œâ€œControl.[Project #]
= tblProjectNames.[Project #] SET tblâ€â€œâ€œâ€œControl.[Project Name] =
[tblProjectNames]![Project Name], tblâ€â€œâ€œâ€œControl.[““ Project Engineer] =
[tblProjectNames]![““ Project Engineer]
WHERE (((tblâ€â€œâ€œâ€œControl.[Project Name]) Is Null) AND ((tblâ€â€œâ€œâ€œControl.
[Project #])=[tblâ€â€œâ€œâ€œControl]![Project #])) OR (((tblâ€â€œâ€œâ€œControl.[““ Project
Engineer]) Is Null) AND ((tblâ€â€œâ€œâ€œControl.[Project #])=[tblâ€â€œâ€œâ€œControl]!
[Project #]));

 

[John Vinson]

UPDATE tbl”“““Control INNER JOIN tblProjectNames ON tbl”“““Control.[Project #]
= tblProjectNames.[Project #] SET tbl”“““Control.[Project Name] =
[tblProjectNames]![Project Name], tbl”“““Control.[““ Project Engineer] =
[tblProjectNames]![““ Project Engineer]
WHERE (((tbl”“““Control.[Project Name]) Is Null) AND ((tbl”“““Control.
[Project #])=[tbl”“““Control]![Project #])) OR (((tbl”“““Control.[““ Project
Engineer]) Is Null) AND ((tbl”“““Control.[Project #])=[tbl”“““Control]!
[Project #]));

You can do this somewhat more simply using the NZ() function to only
update NULL values, leaving the non-NULL values unchanged:

UPDATE tbl”“““Control
INNER JOIN tblProjectNames
ON tbl”“““Control.[Project #] = tblProjectNames.[Project #]
SET
[tbl”“““Control].[Project Name] = NZ(tbl”“““Control.[Project Name],
[tblProjectNames]![Project Name]),
[tbl”“““Control].[““ Project Engineer] =
NZ([tbl”“““Control].[““ Project Engineer],
[tblProjectNames]![““ Project Engineer])
WHERE (((tbl”“““Control.[Project Name]) Is Null);

It's not necessary to compare the Project # in the WHERE clause,
that's already taken care of by the JOIN.

John W. Vinson[MVP]