IIF function, multiple arguments

[Mary]

Is there a way to have more than one argument in an IIF
statement?

I'd like the function to show records that meet the
following criteria -


AppDate=Yes And DeniedDate=Yes
Or
AppDate=No
Or
DisAppDate=Yes And DisDeniedDate=Yes
Or
DisAppDate=No

If these are all true, then show [Date], otherwise leave
null.

I can get the function to work with any one of these
statements, but not all of them together.

Thanks for the help.
Mary

 

[Marshall Barton]

Is there a way to have more than one argument in an IIF
statement?

I'd like the function to show records that meet the
following criteria -


AppDate=Yes And DeniedDate=Yes
Or
AppDate=No
Or
DisAppDate=Yes And DisDeniedDate=Yes
Or
DisAppDate=No

If these are all true, then show [Date], otherwise leave
null.

If you really mean they all have to true (which, by the way
is impossible), then it could be:

=IIf(AppDate = Yes And DeniedDate = Yes And AppDate = No
And DisAppDate = Yes And DisDeniedDate = Yes And DisAppDate
= No, [Date], Null)

If you meant to say if any are true then it would be:

=IIf((AppDate = Yes And DeniedDate = Yes) Or AppDate = No
Or (DisAppDate = Yes And DisDeniedDate = Yes) Or DisAppDate
= No, [Date], Null)

 

[Mary]

I can't use And between each field, I need to use Or as
shown. It is possible, I just don't know if an IIF
function can do this.

If an employee:
1) applied for benefits - And - was denied
Or
2) never applied for benefits
Or
3) applied for disability - And - was denied
Or
4) never applied for disability

There are four separate arguments, and I can get the
results by using separate queries, but I'd like to get
them by using the IIF function if possible.

I don't know if I'm saying this right. Hopefully this
helps clear up what I'm asking.

Thank you,
Mary

-----Original Message-----

Is there a way to have more than one argument in an IIF
statement?

I'd like the function to show records that meet the
following criteria -


AppDate=Yes And DeniedDate=Yes
Or
AppDate=No
Or
DisAppDate=Yes And DisDeniedDate=Yes
Or
DisAppDate=No

If these are all true, then show [Date], otherwise leave
null.

If you really mean they all have to true (which, by the way
is impossible), then it could be:

=IIf(AppDate = Yes And DeniedDate = Yes And AppDate = No
And DisAppDate = Yes And DisDeniedDate = Yes And DisAppDate
= No, [Date], Null)

If you meant to say if any are true then it would be:

=IIf((AppDate = Yes And DeniedDate = Yes) Or AppDate = No
Or (DisAppDate = Yes And DisDeniedDate = Yes) Or DisAppDate
= No, [Date], Null)

 

[Marshall Barton]

I can't use And between each field, I need to use Or as
shown. It is possible, I just don't know if an IIF
function can do this.

If an employee:
1) applied for benefits - And - was denied
Or
2) never applied for benefits
Or
3) applied for disability - And - was denied
Or
4) never applied for disability

There are four separate arguments, and I can get the
results by using separate queries, but I'd like to get
them by using the IIF function if possible.

I don't know if I'm saying this right. Hopefully this
helps clear up what I'm asking.

Thats's what I thought you wanted and I posted the way to do
it in my second example:

=IIf((AppDate = Yes And DeniedDate = Yes) Or AppDate = No
Or (DisAppDate = Yes And DisDeniedDate = Yes) Or DisAppDate
= No, [Date], Null)

If I'm still missing the point, can you explain why that
didn't do what you asked?

 

[Mary]

Thank you. I apologize, I didn't see the second example.

Thanks again for the help.

Mary

-----Original Message-----

I can't use And between each field, I need to use Or as
shown. It is possible, I just don't know if an IIF
function can do this.

If an employee:
1) applied for benefits - And - was denied
Or
2) never applied for benefits
Or
3) applied for disability - And - was denied
Or
4) never applied for disability

There are four separate arguments, and I can get the
results by using separate queries, but I'd like to get
them by using the IIF function if possible.

I don't know if I'm saying this right. Hopefully this
helps clear up what I'm asking.

Thats's what I thought you wanted and I posted the way to do
it in my second example:

=IIf((AppDate = Yes And DeniedDate = Yes) Or AppDate = No
Or (DisAppDate = Yes And DisDeniedDate = Yes) Or DisAppDate
= No, [Date], Null)

If I'm still missing the point, can you explain why that
didn't do what you asked?

 

[Jerry Herman]

It seems like your post includes all of the information
you need. The trick to using multiple criteria in an IIF
statement is to build a single expression that evaluates
to TRUE or FALSE. You would control the order of
evaluation by using parenthesis, as follows:

((AppDate=Yes) And (DeniedDate=Yes))
Or
(AppDate=No)
Or
((DisAppDate=Yes) And (DisDeniedDate=Yes))
Or
(DisAppDate=No)

I've left the carriage returns that you used in your post
for easier readability. Within the IIF statement, you
would enter all of the above (without carriage returns) as
the "expression", as follows:

=IIF("expression", [Date], null)

I hope this works for you!
Jerry