Two IIF criteria

[Sue Compelling]

Hi

Here’s my situation where I’d really appreciate some help...

If my customer has only one status record (using [JobID] for the count)
AND the [JobSubStatus] = “Awaiting Appointment Adviceâ€
then I want the # of days to be calculated as: Now()-[datereceived]

However if there is more than one status record
AND the [JobSubStatus] = “Awaiting Appointment Adviceâ€
then I want the # of days to be calculated from the:

[JobStatusDate] of the [JobSubStatus] where it equals “Appointment
Scheduled†- [datereceived]

Note – this criteria is part of a bigger IIF statement below which works OK
with the criteria so far.

=IIf(IsNull([JobSubStatus]),(Now()-[datereceived]),IIf([JobSubStatus]="Awaiting
Paperwork",(Now()-[dateappt]),IIf([JobSubStatus]="Awaiting
Spares",([JobStatusDate]-[dateappt]),IIf([JobSubStatus]="Awaiting
Contractor",([JobStatusDate]-[dateappt]),([JobStatusDate]-[datereceived])))))

TIA

 

[Mike Painter]

Hi

Here's my situation where I'd really appreciate some help...

If my customer has only one status record (using [JobID] for the
count) AND the [JobSubStatus] = "Awaiting Appointment Advice"
then I want the # of days to be calculated as: Now()-[datereceived]

However if there is more than one status record
AND the [JobSubStatus] = "Awaiting Appointment Advice"
then I want the # of days to be calculated from the:

[JobStatusDate] of the [JobSubStatus] where it equals "Appointment
Scheduled" - [datereceived]

Note - this criteria is part of a bigger IIF statement below which
works OK with the criteria so far.

=IIf(IsNull([JobSubStatus]),(Now()-[datereceived]),IIf([JobSubStatus]="Awaiting
Paperwork",(Now()-[dateappt]),IIf([JobSubStatus]="Awaiting
Spares",([JobStatusDate]-[dateappt]),IIf([JobSubStatus]="Awaiting
Contractor",([JobStatusDate]-[dateappt]),([JobStatusDate]-[datereceived])))))

TIA

I would created a function to do this as it is far easier to read, corrent
and manage.
Just looking at this I don't see wher what you want weould fit and it might
not be possible to obtain the desired results with nested if statements.

I'm to lazy to write it all out but you need to use dCount with an AND to
get what you want.

Iff status = "somevalue" AND dcount (some field, some domain , some
criteria) = 1, do this, dothat)