Update Query with IIF

[Liliane]

I have two tables: tbl_Audits and tbl_AAL_Items.
I need a Yes/No field "Design" is Yes when [Item_Category] is "Design" .
Also, in some cases, I need to keep [Design] is already true as true when
the [Item_category] is not "Design".

Here's my SQL:
UPDATE tbl_Audits INNER JOIN tbl_AAL_Items ON tbl_Audits.Audit_No =
tbl_AAL_Items.Audit_No SET tbl_Audits.Design = IIf([design]=False And
[item_Category]="Design",True);

My problem is this query will set those [Design] as No where their
Item_category is not "Design".

Thanks a million!!!

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

This should set Design to True if it is false AND if Item_Category equals
"Design". I am guessing that Item_Category is in the tbl_AAL_Items since
that table is not needed if Item_Category is in tbl_Audits.


UPDATE tbl_Audits INNER JOIN tbl_AAL_Items
ON tbl_Audits.Audit_No = tbl_AAL_Items.Audit_No
SET tbl_Audits.Design = True
WHERE Item_Category = "Design"
And tbl_Audits.Design = False


Using your IIF expression, you would need this expression
IIf([Design=True] OR
([design]=False And [item_Category]="Design"),True,False)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County