Traumatized by killer NESTED IIFs

MsLady

Traumatized by Access
Local time
Today, 11:56
Joined
Jun 14, 2004
Messages
438
Hello friends,

I have a form that displays a summary of my records (from my lovely work request database).
A textbox named txtRequest should display a concantenation of the workrequestType based on an IIF statement in this matter.

The fields in my table are:
[R1] meaning primary request , [requestDetails] primary request details,
[R2] meaning secondary request, [requestDetails2] secondary request details

I would like my report to display: [R1]-[requestDetails] / [R2]-[requestDetails2] and to omit any that does not exist.
Like if any of the requestDetails is missing, the leading "-" should not be displayed (it confuses my users to think there's something missing). If a secondary request [R2] does not exist, "/" should not be displayed. And obvisiouly there can't be a [requestDetails2] if [R2] does not exist.

Note: [R1] and [R2] are dropdown of the common work requests. Now once they select a request, there's a textbox where they must provide details [requestDetails] if they have any. Sometime they don't provide details, which is okay. But i'd like to display the info in a clean smart manner.

Here's what i currently have and it displays nothing...! comes up blank!
Code:
=IIf(IsNull([R2]),(IIf(IsNull([requestDetails]),[R1],([R1] & "-" & [requestDetails])),([R1] & "-" & [requestDetails] & " /  " & [R2] & "-" & [requestDetails2])))
Here's the kindergaten IIF statement i had before and was working properly before my boss started bugging me.
Code:
=IIf(IsNull([R2]),[R1],([R1] & " /  " & [R2]))

I have a been struggling with this for quite a while now, and i have just lost every hope of doing this on my own, so please can anyone be so kind to help?
 
I'm not sure but I think you might have a parenthesis out of place so that your negative result for the first IIf is hidden within your second IIf

=IIf(IsNull([R2]),(IIf(IsNull([requestDetails]),[R1],([R1] & "-" & [requestDetails]))),([R1] & "-" & [requestDetails] & " / " & [R2] & "-" & [requestDetails2]))
 
I agree with John on the paranthesis but do you also need to check if requestdetails2 is null, i.e. can you have R2 without a requestDetails2?


IIf(IsNull([R2]),(IIf(IsNull([requestDetails]),[R1],([R1] & "-" & [requestDetails]))),(IIf(IsNull([requestDetails2]),[R1] & "-" & [requestDetails] & " / " & [R2],[R1] & "-" & [requestDetails] & " / " & [R2] & "-" & [requestDetails2]))

Brian
 
Brianwarnock said:
I agree with John on the paranthesis but do you also need to check if requestdetails2 is null, i.e. can you have R2 without a requestDetails2?




Brian

Thanks John and Brian. Yes i can have R2 without a requestDetails2, and in that case, i'd like to exclude the leading "-".

I will go try what you have suggested above...
 
Now i get error: wrong number of argument with this. can anyone help? :(

Code:
=IIf(IsNull([requestDetails]) And IsNull([requestDetails2])),(IIf(IsNull([R2]),[R1],([R1] & " / " & [R2])),(IIf(IsNull([requestDetails2]),([R1] & "-" & [requestDetails] & " / " & [R2]),([R1] & "-" & [requestDetails] & " / " & [R2] & "-" & [requestDetails2]))
 
Banana said:
IIF function is good when you just have two conditions, but nested IIF function can make your head asplode.

Consider using Switch function or perhaps Choose function instead.

They're Access query's equilivant of VBA Select Case statement. :)

Ahh *sighs
thanks banana :) that switch looks good, i'll go try it.

My head is really spinning for real, i just went ballistic with curses #$%%$%# while pointing at my monitor 5minutes ago and my coworkers had to come check if i was okay...i hope i get this to work soonest or access will suffer my wrath :mad:

Thanks. I'll let u know how it goes...
 
MsLady said:
Now i get error: wrong number of argument with this. can anyone help? :(

Code:
=IIf(IsNull([requestDetails]) And IsNull([requestDetails2])),(IIf(IsNull([R2]),[R1],([R1] & " / " & [R2])),(IIf(IsNull([requestDetails2]),([R1] & "-" & [requestDetails] & " / " & [R2]),([R1] & "-" & [requestDetails] & " / " & [R2] & "-" & [requestDetails2]))

Again I think it is syntax to do with paranthesis, try

=IIf(IsNull([requestDetails]) And IsNull([requestDetails2]),IIf(IsNull([R2]),[R1],[R1] & " / " & [R2]),IIf(IsNull([requestDetails2]),[R1] & "-" & [requestDetails] & " / " & [R2],[R1] & "-" & [requestDetails] & " / " & [R2] & "-" & [requestDetails2]))

I see I overlooked a case in my original code.:o

Brian
 
Ladies and gentlemen: Allow me to honor the Switch function
z2929060.gif



My code below wasn't hard to put together at all...and it works perfectly.
Nested IIf can perish!!, I found a new friend :D

Banana, i can never thank you enough!! and John. Brian, thanks alot.
icon14.gif


Code:
=Switch((IsNull([R2]) And IsNull([requestDetails2]) And IsNull([requestDetails])),[R1],
(IsNull([R2]) And IsNull([requestDetails2]) And Not IsNull([requestDetails])),([R1] & "-" & [requestDetails]),
(Not IsNull([R2]) And IsNull([requestDetails2]) And Not IsNull([requestDetails])),([R1] & "-" & [requestDetails] & " / " & [R2]),
(Not IsNull([R2]) And IsNull([requestDetails2]) And Not IsNull([R1]) And IsNull([requestDetails])),([R1] & " / " & [R2]),
(Not IsNull([R2]) And Not IsNull([requestDetails2]) And Not IsNull([R1]) And Not IsNull([requestDetails])),([R1] & "-" & [requestDetails] & " / " & [R2] & "-" & [requestDetails2]),
(not IsNull([R2]) And not IsNull([requestDetails2]) And not IsNull([R1]) And IsNull([requestDetails])),([R1] & " / " & [R2]& "-" & [requestDetails2]))

sweeeet ;)
 
Also, you might consider using + instead of &. The difference is that + does not propegate nulls. For example, If I had two fields, FirstName and LastName, and LastName = "Jones" and FirstName was null:

[LastName] & ", " & [FirstName] = Jones,

[LastName] & (", " + [FirstName]) = Jones

Can save you a lot of work testing for nulls.
 
Hmn..interesting! Didn't know that. I'll incorporate that into my code.
Thank you ejstefl :D I have learned alot today! thanks dear
 
So your code could be reduced to:

=[R1] & ("-" + [requestDetails]) & ( " / " + [R2]) & ("-" + [requestDetails2])
 
Thanks ejstefl,

I have practised it :D

Code:
=Switch((IsNull([R2]) And IsNull([requestDetails2]) And IsNull([requestDetails])),[R1],
(IsNull([R2]) And IsNull([requestDetails2]) And Not IsNull([requestDetails])),([R1] & ("-"+[requestDetails])),
(Not IsNull([R2]) And IsNull([requestDetails2]) And Not IsNull([requestDetails])),([R1] & ("-"+[requestDetails]) & (" / "+[R2])),
(Not IsNull([R2]) And IsNull([requestDetails2]) And Not IsNull([R1]) And IsNull([requestDetails])),([R1] & (" / "+[R2])),
(Not IsNull([R2]) And Not IsNull([requestDetails2]) And Not IsNull([R1]) And Not IsNull([requestDetails])),([R1] & ("-"+[requestDetails]) & (" / "+[R2]) & ("-"+[requestDetails2])),
(Not IsNull([R2]) And Not IsNull([requestDetails2]) And Not IsNull([R1]) And IsNull([requestDetails])),([R1] & (" / "+[R2]) & ("-"+[requestDetails2])),
(IsNull([R2]) And Not IsNull([requestDetails2]) And Not IsNull([requestDetails])),([R1] & ("-"+[requestDetails]) & (" / "+[requestDetails2])))

Thanks all :)
 
No no - all you need is the one line I gave you. You can get rid of all that other code... The + removes the need to use Ifs (or Switch) to test for nulls. Give it a shot!
 
Hmm! The switch does not look any simpler than nested IIFs in this example, but I do like the look of that +, pity I learnt about it after I retired:D

Brian
 
ejstefl said:
No no - all you need is the one line I gave you. You can get rid of all that other code... The + removes the need to use Ifs (or Switch) to test for nulls. Give it a shot!
:eek:

wow! this is amazing! Here's all i have now :eek:
Code:
=[R1] & ("-" + [requestDetails]) & ( " / " + [R2]) & ("-" + [requestDetails2])
This is great cos i had to use that code in 3 summary forms, i have gone around changing them already! Thank you so much!!! :cool:
icon14.gif


lovely!
 
Glad to help - I think that the + function is very under-utilized, especially since it can simplifly things so much!
 
Brianwarnock said:
Hmm! The switch does not look any simpler than nested IIFs in this example, but I do like the look of that +, pity I learnt about it after I retired:D

Brian
Not at all. the switch wasn't hard to put together ([logic],[value],[logic2],[value2]....). Took me about 8minutes in this example. But the nested IIF is a far cry away from that. You had to worry about nesting it properly and driving urself crazy. I had spent 2 fustrating working days on it, so u can imagine my pain. I like switch better and "+" method the best :D I will use "IIF" only at gunpoint :o

The + is definately amazing and my bestfriend in this case. It's great to know about the switch too.

Thanks!!! you rock. all of you! *hugs
 
MsLady said:
Not at all. the switch wasn't hard to put together ([logic],[value],[logic2],[value2]....). Took me about 8minutes in this example. *hugs

OK I was going on a quick scan of your code, after I had copied it into a word doc so that I could see all of it without scrolling the beauty of it became more clear, thanks for the feedback.

brian
 
You would think that a good reference book would call attention to this sort of thing, but nooooo....

Thanks for the tip on +. I might actually use that someday. :)
 

Users who are viewing this thread

Back
Top Bottom