Help with nested IIF statement in a query

Hello all.

This expression is in my query and it works well. However, if both
continues are false, I want it to say "Not Yet Issued". I've added
that and getting an #ERROR.

Any ideas?

TimeToIssue: IIf(Not
IsNull([ApprovedStandardTransDate]),DateDiff("d",[REceivedTransdate],[ApprovedStandardTransDate]),IIf(Not
IsNull([ApprovedSubStandardTransDate]),DateDiff("d",[REceivedTransdate],[ApprovedSubStandardTransDate]),"Not
Yet Approved"))

Please also make note that:

TimeToIssue: IIf(Not
IsNull([ApprovedStandardTransDate]),DateDiff("d",[ReceivedTransdate],[ApprovedStandardTransDate]),"TEST")


works

And

TimeToIssue: IIf(Not
IsNull([ApprovedSubStandardTransDate]),DateDiff("d",[ReceivedTransdate],[Approved
SubStandardTransDate]),"TEST")

works

It's only when I try to nest them I get the #Error


BerkshireGuy wrote:

What data type do you want the TimeToIssue to be?

If you want to treat it like a number, then assigning text like "Not Yet
Approved" is going to foul it up. Better to leave it as Null, and then put
that into the text box where it will be displayed. For that result, try:
TimeToIssue: DateDiff("d",[REceivedTransdate],
Nz([ApprovedStandardTransDate], [ApprovedSubStandardTransDate])

If you don't care that is is treated as text (e.g. 2 sorts after 10), try:
TimeToIssue: Nz(DateDiff("d",[REceivedTransdate],
Nz([ApprovedStandardTransDate], [ApprovedSubStandardTransDate]), "Not Yet
Approved")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BerkshireGuy" <bdaoust@yahoo.comwrote in message
news:1155215241.112134.280440@m79g2000cwm.googlegr oups.com...

Allen,

Thank you. I tried your suggestion and it work. I appreciate your
quick response.

Have a great day,
Brian



Allen Browne wrote: