Update Fields If FROM Table Fields Are NOT NULL

[Handyman]

Want to update T2 fields ONLY IF T1 files are NOT NULL. Since
T1.Field2 is NULL then T2.Field2 will not be updated but Field1 and
Field3 will be updated.

I'm joining tables with their fldSSN fields.

T1
Field1 NOT NULL
Field2 NULL
Field3 NOT NULL

T2
Field1 NOT NULL
Field2 NOT NULL
Field3 NOT NULL

Here's what I've tried but it still updates T2.Field2 to NULL.

UPDATE T2 INNER JOIN T1 ON T2.fldSSN = T1.fldSSN
SET T2.Field1 = [T1]![Field1], T2.Field2 = [T1]![Field2], T2.Field3 =
[T1]![Field3]
WHERE ((([T1]![Field1]) Is Not Null)) OR ((([T1]![Field2]) Is Not
Null)) OR ((([T1]![Field3]) Is Not Null));

Any help would be appreciated.

Ron

 

[John Spencer]

UPDATE T2 INNER JOIN T1
ON T2.fldSSN = T1.SSN
SET T2.Field1 = NZ([T1].[Field1],[T2].[Field1])
, T2.Field2 = NZ([T1].[Field2],[T2].[Field2])
, T2.Field3 = NZ([T1].[Field3],[T2].[Field3])
WHERE T1.Field1 is Not Null
OR T1.Field2 is not null
OR T1.Field3 is not null

Either that or use three queries to update one field at a time,


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Handyman]

UPDATE T2 INNER JOIN T1
ON T2.fldSSN = T1.SSN
SET T2.Field1 = NZ([T1].[Field1],[T2].[Field1])
, T2.Field2 = NZ([T1].[Field2],[T2].[Field2])
, T2.Field3 = NZ([T1].[Field3],[T2].[Field3])
WHERE T1.Field1 is Not Null
OR T1.Field2 is not null
OR T1.Field3 is not null

Either that or use three queries to update one field at a time,

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Want to update T2 fields ONLY IF T1 files are NOT NULL. Since
T1.Field2 is NULL then T2.Field2 will not be updated but Field1 and
Field3 will be updated.

I'm joining tables with their fldSSN fields.

T1
Field1 NOT NULL
Field2 NULL
Field3 NOT NULL

T2
Field1 NOT NULL
Field2 NOT NULL
Field3 NOT NULL

Here's what I've tried but it still updates T2.Field2 to NULL.

UPDATE T2 INNER JOIN T1 ON T2.fldSSN = T1.fldSSN
SET T2.Field1 = [T1]![Field1], T2.Field2 = [T1]![Field2], T2.Field3 =
[T1]![Field3]
WHERE ((([T1]![Field1]) Is Not Null)) OR ((([T1]![Field2]) Is Not
Null)) OR ((([T1]![Field3]) Is Not Null));

Any help would be appreciated.

Ron- Hide quoted text -

- Show quoted text -

Works great John! Thanks.