Traumatized by killer NESTED IIFs

[MsLady]

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!

=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.

=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?

 

[John Big Booty]

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]))

 

[Brianwarnock]

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

 

[MsLady]

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...

 

[MsLady]

Now i get error: wrong number of argument with this. can anyone help?

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

 

[Banana]

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.

 

[MsLady]

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

Thanks. I'll let u know how it goes...

 

[Brianwarnock]

Now i get error: wrong number of argument with this. can anyone help?

=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.

Brian

 

[MsLady]

Ladies and gentlemen: Allow me to honor the Switch function

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

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

=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

 

[ejstefl]

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.

 

[MsLady]

Hmn..interesting! Didn't know that. I'll incorporate that into my code.
Thank you ejstefl I have learned alot today! thanks dear

 

[ejstefl]

So your code could be reduced to:

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

 

[MsLady]

Thanks ejstefl,

I have practised it

=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

 

[ejstefl]

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!

 

[Brianwarnock]

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

Brian

 

[MsLady]

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!

wow! this is amazing! Here's all i have now

=[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!!!

lovely!

 

[ejstefl]

Glad to help - I think that the + function is very under-utilized, especially since it can simplifly things so much!

 

[MsLady]

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

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 I will use "IIF" only at gunpoint

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

 

[Brianwarnock]

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

 

[Banana]

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.