By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,663 Members | 762 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,663 IT Pros & Developers. It's quick & easy.

Help with nested IIF statement in a query

P: n/a
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"))

Aug 10 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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:
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"))
Aug 10 '06 #2

P: n/a
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" <bd*****@yahoo.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
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"))

Aug 10 '06 #3

P: n/a
Allen,

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

Have a great day,
Brian

Allen Browne 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" <bd*****@yahoo.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
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"))
Aug 10 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.