Solved Calculated Field based on 2 conditions

[Lochwood]

Easy one,

I have 2 conditions that will create and "*" when conditions are met. so 3 fields must be NotNull and checkbox must be unticked or checkbox is ticked

When i use the or statement all i get is -1 so thinking my syntax is wrong.

1: IIf([Security_Checked_Date] Is Not Null,IIf([Security_Confirmed_Date] Is Not Null,IIf([Access_Date] Is Not Null,IIf([Security_Not_Applicable]=False,"*")))) or IIf([Security_Not_Applicable]=True,"*")

 

[theDBguy]

Hi. I'm confused. If the checkbox can be either checked or not checked, then why even bother to add it to your conditions?

 

[gemma-the-husky]

-1 is "true", so your expression must be evaluating to true
I think your expression is being evaluated as

iif firstpartof expression OR iif secondpartofexpression, and it is giving you a boolean result of true.

what you need is to turn it around so you have a single iif

Iif (securitynotapplicable,"Result 1", {else whatever you check for securitynotapplicable of false})

 

[Lochwood]

Hi. I'm confused. If the checkbox can be either checked or not checked, then why even bother to add it to your conditions?

if its unchecked then the other 3 conditions must not be null if its checked then they dont basically NA checkbox.

 

[gemma-the-husky]

if its unchecked then the other 3 conditions must not be null if its checked then they dont basically NA checkbox.

Yes it made sense to me, but I am pretty sure the problem is caused by the way you have actually written the expression.
I don't like nested iifs for that reason. I would much rather call a function in the query

Query expression
Result: ConditionResult ([Security_Checked_Date],[Security_Confirmed_Date],[Access_Date],[Security_Not_Applicable])

function ConditionResult(SecDate,SecConfDate,ADate,SecNA) as string
'check the parameters within the function
end function

Note that you need to test how your function deals with any nulls.

 

[smaction]

-1 is "true", so your expression must be evaluating to true
I think your expression is being evaluated as

iif firstpartof expression OR iif secondpartofexpression, and it is giving you a boolean result of true.

what you need is to turn it around so you have a single iif

Iif (securitynotapplicable,"Result 1", {else whatever you check for securitynotapplicable of false})

I saw you mentioned "-1 is true". In my test code I am getting 1. I thought 0 was false, do you know what 1 is?

TIA

 

[plog]

Agree with gemma, it's time for a custom function.

You get to nest 1 Iif statement inside another; more than that and its time for a function.

 

[cheekybuddha]

You can always add them together, pseudocode:
IIf(IsNull(Security_Checked_Date + Security_Checked_Date + Access_Date), AtLeastOneOfThemIsNull, NoneOfThemIsNull)