IIF Statement in query

[mariposanegra225]

Hi Everyone,

I have a column with 3 possible values, exempt, passed and null. I am trying to write a statement within a query that will populate a new column dependent on the values in the original field. The result for the records with "passed" depend on specific dates entered on a form, while the "exempt" and null entries do not and should not rely on the dates entered. This is what I have:



RFTStatus: (IIf([respiratoryfittest]![status date] Between [Forms]![ahaswitchboard]![From Date] And [Forms]![ahaswitchboard]![thru Date] And [respiratoryfittest]![status]="passed", "complete")) Or (iif([respiratoryfittest]![status]="passed","Complete","Incomplete"))

I unsuccessfully end up with "-1" for each of the records. What am I doing wrong?

[June7]

This is two IIf expressions joined by an OR operator. This evaluates as: This OR That. This further evaluates as True because there is either This or That, hence each record displays -1 (True).

Trying to follow logic of the expression. Seems that regardless of date, if [status]="passed" should return "Complete".

Is the date range intended to filter the data and show only those records within the range?

[mariposanegra225]

Thanks for the quick reply.

I only want the "passed" records to return a "complete" result if the date falls within the filter range. If the date is outside of the range entered, the result should be "incomplete". The other two values, NULL and Exempt are not dependent on the date range. If there is a record with "exempt", the result should be "complete" and if a record has a null value in the status field, the result should be "incomplete".

Even though it is still wrong, I see that I made a mistake on the last part (I had "passed" in both status fields). What I meant to enter was:


RFTStatus: (IIf([respiratoryfittest]![status date] Between [Forms]![ahaswitchboard]![From Date] And [Forms]![ahaswitchboard]![thru Date] And [respiratoryfittest]![status]="passed", "complete")) Or (iif([respiratoryfittest]![status]="exempt","Complete","Incomplete"))

[June7]

And that still gives -1 result on every record (still: 'This' OR 'That').

Try this:

RFTStatus: IIf(([respiratoryfittest]![status date] Between [Forms]![ahaswitchboard]![From Date] And [Forms]![ahaswitchboard]![thru Date] And [respiratoryfittest]![status]="passed") OR [respiratoryfittest]![status]="exempt","Complete","Incomplete")

[mariposanegra225]

There it is! Many many thanks!!

[mariposanegra225]

Could I take this one step further to include another condition like below:

RFTStatus: IIf((([respiratoryfittest]![status date] Between [Forms]![ahaswitchboard]![From Date] And [Forms]![ahaswitchboard]![thru Date] And [respiratoryfittest]![status]="passed") Or [respiratoryfittest]![status]="exempt","Complete") OR [tpeoplesoft matched to tcostcenter]![pay status]="p" or [tpeoplesoft matched to tcostcenter]![pay status]="l","On Leave","incomplete")

[June7]

[tpeoplesoft matched to tcostcenter] is a table/query included in this query by join? Then yes, can include that condtion.

However, syntax of the new expression is wrong. You almost have another 'This' OR 'That' situation. Does it error when running query?

An IIf() function has structure:
IIf(condition, result if true, result if false)

Nested IIf:
IIf(condition, result if true, IIf(another condition, result if true, result if false))

Example of an IIf with multiple OR/AND conditions:
IIf((condition1 AND (condition2 OR condition3)) OR condition4 OR condition5, result if true, result if false)

Your latest expression does not fit any of these.

[mariposanegra225]

Success! Here's what I came up with. Gracias!

RFTStatus: IIf(([respiratoryfittest]![status date] Between [Forms]![ahaswitchboard]![From Date] And [Forms]![ahaswitchboard]![thru Date] And [respiratoryfittest]![status]="passed") Or [respiratoryfittest]![status]="exempt","Complete",IIf([tpeoplesoft matched to tcostcenter]![pay status]="p" Or [tpeoplesoft matched to tcostcenter]![pay status]="l","on leave","Incomplete"))