Nested IIf statement does not retrieve all information 1

[romann]

I am trying to set up a status field within a query where only the latest date of the following three fields appears, but the PERRR1 and PERRR2 fields may or may not any have an entry.

PERReceived
PERRR1
PERRR2

So if PERRR2 is not entered then it should enter PERRR1 and if PERRR1 that isn't entered it should enter PERReceived. PERReceived will always have a date.

I've tried a nested iif statement in the query that looks like this:

Status: IIf(IsNull([PERRR2]),[PERRR1],IIf(IsNull([PERRR1]),[PERReceived],[PERRR2]))

The output is that I get dates if there is an entry for PERRR2 or PERRR1 but the field is blank otherwise. I would like if there is no entry for PERRR2 or PERRR1 that the status field states the PERReceived.

Thanks for you help.

 

[PHV]

What about this ?
Status: Nz(PERRR2,Nz(PERRR1,PERReceived))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PaulBricker]

Try this. A conditional statement stops afer the first True value is returned so you statement stops if PERRR2 is Null and only gets by that part of the statement when is is not null.

Status: IIf(IsNull([PERRR2]) And NotIsNull([PERRR1),[PERRR1],IIf(IsNull([PERRR2]) And IsNull([PERRR1]),[PERReceived],[PERRR2]))

Paul

 

[PHV]

If you insist with IIF ...
Status: IIf(IsNull(PERRR2), IIf(IsNull(PERRR1),PERReceived,PERRR1), PERRR2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[romann]

Thanks it worked great.