May 6, 2011 at 8:47 am
I have a working query and report in MS Access that I have to convert to Crystal Reports to run against the SQL back end. It uses the following formula as one of the fields, and I've added linebreaks & indentation to help show the structure of the nesting and either -T- to designate the "Truepart" and -F- to designate the "Falsepart",
IIf([PhoneSolve]=-1 Or [PhoneSolve]=0,
-T-IIf([TSR_ID] Is Null,
-T-IIf([ESR_ID] Is Null,
-T-"CS",
-F-"ESR"),
-F-IIf([ESR_ID] Is Not Null,
-T-"ESR",
-F-"TSR")
),
-F-IIf([TSR_ID] Is Not Null,
-T-IIf([ESR_ID] Is Null,
-T-"TSR",
-F-"ESR")
)
)
Note the lack of a "Falsepart" for the next to last IIf function. No error occurs, but I have to wonder why Access 2003 isn't erroring out, as the function is documented as having that parameter listed as "required".
Can anyone shed any light on this? I won't have difficulty creating the needed nested CASE statements in T-SQL, but I'd like to know for sure how this function actually behaves to be sure I code it correctly...
This is an unfamiliar data source I've just started working with, and is also a "legacy environment", so answers are in short supply. Any/all help appreciated. A search of the web for nested IIf and missing falsepart returned no useful results.
Steve
(aka sgmunson)
🙂
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 6, 2011 at 11:05 am
I've used nested IIf() statments in Access since version 2, and it always seemed to give me an error without a false part. However I constructed a statement which I believe is identical to your post in Access 2007 and it happily accepts it. Just for the record it is:
REsult: IIf([PhoneSolve]=-1 Or [PhoneSolve]=0,IIf([TSR_ID] Is Null,IIf([ESR_ID] Is Null,"CS","ESR"),IIf([ESR_ID] Is Not Null,"ESR","TSR")),IIf([TSR_ID] Is Not Null,IIf([ESR_ID] Is Null,"TSR","ESR")))
I also created a sample data table and ran a query against that and it produced this result:
Result PhoneSolve TSR_ID ESR_ID
CS-1
TSR-1 1
ESR-1 1
ESR-1 1 1
CS0
TSR0 1
ESR0 1
ESR0 1 1
2
TSR2 1
2 1
ESR2 1 1
Looking at the logic, it seems likely that the PhoneSolve field is boolean, in which case the false part of the outermost IIf() never gets executed, but even if that is the case, I'm pretty sure Access isn't smart enough to know to ignore the false case. Also note that for the True condition on the outermost IIf(), the statement IIf([ESR_ID] Is Not Null,"ESR","TSR") isn't necessary, and could simply be replaced by "ESR" - as the sample data indicates. I'll do some further research on this surprising situation and post back if I find anything. Meanwhile, I think you might want to look carefully at the logic involved to make sure all possible cases are being dealt with.
EDIT: Sorry - that part about the statement not being necessary is incorrect - I was looking at the logic incorrectly. But the mystery of the missing false part still persists.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
May 6, 2011 at 2:56 pm
Thanks for the analysis. It would be interesting to hear what sayeth Microsoft on this, given that Access 2007 displays the same behavior. The "hole" in their formula evaluation in this case is wide enough to drive a semi through. Since my last post, I've been able to prove that the results I get with my nested CASE statements now produce the same results as this query does, although whether that would continue to be true all the time remains to be seen, as a "hole" like this might just as easily produce a random result as an incorrect one, with little or no opportunity to tell the difference. I have also discovered some fine print in the actual report output that identifies a "Resolved by" code of 'NS' that I wasn't aware of before, and it stands for "Not Specified", so until I'm told otherwise, that's what I've plugged into the missing "Falsepart". Here's the nested CASE statement I have for this at the moment.
CASE
WHEN PhoneSolve IN (-1, 0) THEN
CASE
WHEN TSR_ID IS NULL THEN CASE WHEN ESR_ID IS NULL THEN 'CS' ELSE 'ESR' END
WHEN ESR_ID IS NOT NULL THEN 'ESR'
ELSE 'TSR'
END
WHEN TSR_ID IS NOT NULL THEN
CASE
WHEN ESR_ID IS NULL THEN 'TSR'
ELSE 'ESR'
END
ELSE 'NS'
END AS [Resolved by],
For now, I have a working query, but I'll keep my eye on this topic for any updates that might indicate you've found either a solution, work-around, or that MS is aware of this gaping void in their low-end database product. I may also occasionally try different key words for further Google searches, as there's TONS of stuff on the web about IIf, so I may be looking for a needle in a haystack. It would SEEM that someone else MUST have run into this before, as IIf gets a LOT of use within Access, and I've been aware of this lack of proper operation for a lot of years, but I've never before had to figure out what it does under these circumstances because every other time I encountered it, I was getting incorrect results, found the missing piece, and supplied it. Thanks again, and let's hope one of us finds some details on this nasty little gotcha...
Steve
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply