logical operators in a query

[gutmj]

Dear All

I am using the below expression in an update query:

iif((
([tblItemMaster]![RuleMet1] = "Yes" AND [tblItemMaster]![RuleMet2] = "No")
OR ([tblItemMaster]![RuleMet1] = "No" AND [tblItemMaster]![RuleMet2] = "Yes")
),
[tblItemMaster]![ItemQualified] ="No", "")

I have records in tblItemMaster that meet the criteria:
([tblItemMaster]![RuleMet1] = "Yes" AND [tblItemMaster]![RuleMet2] = "No")
OR
([tblItemMaster]![RuleMet1] = "No" AND [tblItemMaster]![RuleMet2] = "Yes")

However [ItemQualified] does not get updated to "No" in tblItemMaster.

I am not sure if I used correct syntax for logical operators.
Would appreciate your help. Thank you.

 

[plog]

I don't think you need to reference the table name and field that you are updating inside the conditional statement (i.e. [tblItemMaster]![ItemQualified] ="No", "")

Also, instead of an update query, make it a select query and bring what you want the field to update to into its own field to see what results it gives. Then once you have it worked out and all the records in the select query are showing correctly, make it an update query and move your code back to where it is now.

Also, if RuleMet1 and RuleMet2 cannot be blank and can only be Yes or No, then you can simplify your condition like this:

iif([tblItemMaster]![RuleMet1] <> [tblItemMaster]![RuleMet2],"No", "")

 

[spikepl]

Are you sure the fields are text containing "Yes" or "No"? Or just boolean fields containing Yes or No?

 

[MSAccessRookie]

Dear All

I am using the below expression in an update query:

iif((
([tblItemMaster]![RuleMet1] = "Yes" AND [tblItemMaster]![RuleMet2] = "No")
OR ([tblItemMaster]![RuleMet1] = "No" AND [tblItemMaster]![RuleMet2] = "Yes")
),
[tblItemMaster]![ItemQualified] ="No", "")

I have records in tblItemMaster that meet the criteria:
([tblItemMaster]![RuleMet1] = "Yes" AND [tblItemMaster]![RuleMet2] = "No")
OR
([tblItemMaster]![RuleMet1] = "No" AND [tblItemMaster]![RuleMet2] = "Yes")

However [ItemQualified] does not get updated to "No" in tblItemMaster.

I am not sure if I used correct syntax for logical operators.
Would appreciate your help. Thank you.

As written, this Query would not update Your Field with a value of "No". The format of the IIf() Function is as follows:

IIf(Condition to Test, Value if TRUE, Value if FALSE)

This makes your IIf() Statement as follows:

IIf((([tblItemMaster]![RuleMet1] = "Yes" AND [tblItemMaster]![RuleMet2] = "No") OR ([tblItemMaster]![RuleMet1] = "No" AND [tblItemMaster]![RuleMet2] = "Yes")), [tblItemMaster]![ItemQualified] ="No", "")

[tblItemMaster]![ItemQualified] ="No" is an expression with a value of either TRUE or FALSE, not "No". The following might be what you should use instead:

IIf((([tblItemMaster]![RuleMet1] = "Yes" AND [tblItemMaster]![RuleMet2] = "No") OR ([tblItemMaster]![RuleMet1] = "No" AND [tblItemMaster]![RuleMet2] = "Yes")), "No", "")

 

[gutmj]

thanks guys, MSAccessRookie...now it's clear. Thanks for your help