CODEWITHSUNDEEP
sqlms-access
Tai Lung
Tai Lung

Reputation: 89

Multiple IIF Criteria in Access

I have two fields A and B. I want an IIf condition together to form a field

I am trying to combine two following conditions :

IIF(A AND B) IS not NULL,0,0) As Col

IIF(A Is not NULL AND B IS NULL),1,0) As Col

How Can I combine these two conditions to form the field Col?

Upvotes: 0

Views: 7058

Answers (3)

ImagineMBE
ImagineMBE

Reputation: 470

Rather than nested IIFs, I like to use switch for readability:

Switch(
A is not null and B is not null,0,
A is not null and B is null,1,
True,0)

Here's the MS help on the switch function: https://support.office.com/en-us/article/Switch-Function-d750c10d-0c8e-444c-9e63-f47504f9e379

"True" will always resolve to True, so it acts as an ELSE in this case.

Upvotes: 2

finjo
finjo

Reputation: 376

General code example to fit with the original question:

IIF((Not IsNull(A) AND Not IsNull(B)),0,IIF((Not IsNull(A) AND B IS NULL),1,0))

Specific answer for your code:

IIF((Not IsNull(Laufzettel.EPolicierungVSL) AND Not IsNull(Laufzettel.DAnlageSchwebeVSL)),0,IIF((Not IsNull(Laufzettel.EPolicierungVSL) AND Laufzettel.DAnlageSchwebeVSL IS NULL),1,0)) AS dunkel

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Something like this:

IIF(A IS not null and B IS not NULL, 1, IIF(A Is not NULL AND B IS NULL),1,0)) As Col

Upvotes: 0

Related Questions