Nested IIF in Query 1

[iowabuckmaster]

This works.
Bingo: IIf([LicenseType]="7","Yes",
IIf([LicenseType]="8","Yes","No"))

This does NOT, returns #error for the field value.
Days Rec to Issue or Reject:
IIf([issue_date] Is Not Null,[Date Info Rec]-[issue_date],
IIf([reject_date] Is Not Null,[Date Info Rec]-[reject_date],"No Dates Entered"))

If I put in a literal value intead of [Date Info Rec]-[reject_date] in the 2nd IIF it will return the No Dates Entered when neither field has a date in it. Why is that?

I have looked and researched this too long. Any insight would be greatly aprreciated.





"I'm just glad I had my bow in hand, from the time I saw him till he was down was about 45 seconds!" Nov. 1st 157 7/8 Typ

 

[rjoubert]

Try this...

IIf(Not IsNull([issue_date]), [Date Info Rec]-[issue_date],
IIf(Not IsNull([reject_date]), [Date Info Rec]-[reject_date], "No Dates Entered"))

 

[iowabuckmaster]

Same exact error, below (what you sent) returns field value of #error

IIf(Not IsNull([issue_date]), [Date Info Rec]-[issue_date],
IIf(Not IsNull([reject_date]), [Date Info Rec]-[reject_date], "No Dates Entered"))

But Below returns a value of No Dates Entered, Just by putting the quotes around the true part of the 2nd IIF.

IIf(Not IsNull([issue_date]), [Date Info Rec]-[issue_date],
IIf(Not IsNull([reject_date]), "[Date Info Rec]-[reject_date]", "No Dates Entered"))

Hmmm...




"I'm just glad I had my bow in hand, from the time I saw him till he was down was about 45 seconds!" Nov. 1st 157 7/8 Typ

 

[rjoubert]

Have you double checked your field references?

 

[PHV]

Problem is that "No Dates Entered" is not a number.

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

 

[iowabuckmaster]

YES

I can break the IIF into to separate fields and it works.

Days Rec to Issue: IIf([issue_date] Is Not Null,[Date Info Rec]-[issue_date],"No Issue Date Entered")

Days Rec to Reject: IIf([reject_date] Is Not Null,[Date Info Rec]-[reject_date],"No Reject Date Entered")

I have a field I gave a better name to
Date Info Rec: Date_Stamp
so I got rid of it and put in actual field name of Date_Stamp but it made no difference.

Unless I figure this out I'll go with the two field option.
Any ideas.




"I'm just glad I had my bow in hand, from the time I saw him till he was down was about 45 seconds!" Nov. 1st 157 7/8 Typ

 

[iowabuckmaster]

PH

I put in 999 instead of "No Dates Entered" and you are correct, in that it now returns 999 when there are no dates.

WHY does that matter if it a literal field or number?

It doesn't matter when the IIF is in two statements as I stated in earlier post.

Is there a solution then for the stacked IIF.



"I'm just glad I had my bow in hand, from the time I saw him till he was down was about 45 seconds!" Nov. 1st 157 7/8 Typ

 

[Golom]

It matters because Dates are numbers ... as is the result of subtracting two dates. You have an IFF that potentially returns a a number or a string (i.e. "No Dates Entered"). If you want to do this then convert the date expression to text so that they are compatible.

IIf([issue_date] IS NOT NULL, CStr([Date Info Rec]-[issue_date]),
IIf([reject_date] IS NOT NULL, CStr([Date Info Rec]-[reject_date]), 
    "No Dates Entered"))

 

[iowabuckmaster]

Golom, As talking here we decided the two sides of the aurgument could NOT be different datatypes. The Cstr fixed it and it works.

Thank You all. It is done!

"I'm just glad I had my bow in hand, from the time I saw him till he was down was about 45 seconds!" Nov. 1st 157 7/8 Typ