Update Fields If FROM Table Fields Are NOT NULL


H

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
 
Ad

Advertisements

J

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
'====================================================
 
Ad

Advertisements

H

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top