Concatenating Nested IIfs!

[Ally]

I have a very long IIf statement - thought I'd try the first part before going any further to make sure I'd got all the syntax right ... and found that it's wrong already.

I am using "Not Is Null" and "And" and for some reason (which I know is probably staring me in the face), I cannot get it right. There is only one argument because it's followed by another IIf (there will be 4 in all).

=IIf(Not IsNull([ErrorCat]) And (IsNull([OtherErrors]),[ErrorCat],IIf(IsNull([ErrorCat]) And (IsNull([OtherErrors])," "))))

 

[Rich]

Which operating systems used "dinosaur" as a programming language?

 

[Ally]

If you reorganize your logic, the IIf() will be simpler. I changed the first condition from compound condition to a simple positive one and that made the next condition also a single. An IIf() can be nested in either/both the true and false parts.

Thank you Pat, but could I ask just a couple of questions please?

What does "made the next condition also a single" mean.

Your reorganization of the statement looks a lot neater but from the way it's written I would understand it "If ErrorCat IsNull print it, but I know that's not right because it's "if it's true then print it, otherwise don't"! Would it be possible for you to explain how this works.

=IIf(IsNull([ErrorCat]), IIf(IsNull([OtherErrors])," ",[OtherErrors]),[ErrorCat])

Thank you.

 

[simongallop]

IIf(IsNull([ErrorCat]), IIf(IsNull([OtherErrors])," ",[OtherErrors]),[ErrorCat])

The formula is saying in VBA format:

{Test ErrorCat}
If IsNull ErrorCat then
{True Bit ie ErrorCat = Null}
........{Test OtherErrors}
........If IsNull OtherErrors then
................{True Bit ie OtherErrors = Null (ErrorCat still = Null)}
................{Do nothing}
................" "
........{False bit ie OtherErrors has something (ErrorCat still = Null)}
........Else
................[OtherErrors]
........{Close the OtherErrors If statement}
........End If
{False bit ie ErrorCat has something}
{This means that OtherErrors is not being tested as it is only tested when ErrorCat is Null}
Else
........[ErrorCat]
{Close the If statement}
End If

Summarise:
2 * Null = " "
IsNull(ErrorCat) AND IsNotNull(OtherErrors) = OtherErrors
IsNotNull(ErrorCat) AND Don't give a monkeys about OtherErrors = ErrorCat

HTH

 

[cogent1]

As a fellow-Colcestrian from Abbeygate Street, allow me to butt in


In your original expression, you had to use two conditions linked by AND statements twice to achieve your ends. In the revised expression, they are replaced by single conditions (no ANDs involved)

In the new expression, the TRUE part can be either " " or [Other Errors] depending on whether [Other Errors] is Null or not.

The FALSE part always returns [Error CAT]. Therefore [Error cat] will always be returned when [Error Cat] is NOT null.

When both are null " " is returned


When [Error Cat] is null and [Other Errors] isn't , [Other Errors] is returned .



Thus, this expression seems to do the job expected:

 

[Ally]

Thank you Harry - that is a real help having it spelled out like that.

And thank you Cogent - "Colcestrians Unite"! ... Cardiff's a long way from 'home'.

The revised version is great because it's actually covering three of my criteria when initially I thought it was only doing two, ... ... but, if I may ask for a bit more help please, I am having trouble adding an extra one.

I need to add if both are Not Null then [ErrorCat] & Chr(13) & Chr(10) & [OtherErrors]

I thought that I wouldn't need to add any more IIf's as it would be a natural part of testing the IIf's that were already there, (although I did try it first) so then I tried:

IIf(IsNull([ErrorCat]),IIf(IsNull([OtherErrors]),"",[OtherErrors]),[ErrorCat],[ErrorCat] & [OtherErrors]))

It says I have the wrong number of arguments. Sorry, I haven't completely got this yet.

 

[simongallop]

IIf(IsNull([ErrorCat]),IIf(IsNull([OtherErrors]),"",[OtherErrors]),[ErrorCat],[ErrorCat] & [OtherErrors]))

It's the 4 part iif statement again!! Remeber IIf is 3 parts

For the main statement you have:

Part1 Testing:IsNull([ErrorCat]),
Part2 Do this when True:IIf(IsNull([OtherErrors]),"",[OtherErrors]),
Part3 Do this when False:[ErrorCat],
Part4 Crash the computer:[ErrorCat] & [OtherErrors]))

The following statement will give this result:

IsNull ErrorCat AND IsNull OtherErrors = ""
IsNull ErrorCat AND IsNotNull OtherErrors = OtherErrors
IsNotNull ErrorCat AND IsNull OtherErrors = ErrorCat
IsNotNull ErrorCat AND IsNotNull OtherErrors = ErrorCat & Chr(13) & Chr(10) & [OtherErrors]

IIf(IsNull([ErrorCat]),IIf(IsNull[OtherErrors])," ",[OtherErrors]),IIf(IsNull([OtherErrors]),[ErrorCat],[ErrorCat] & Chr(13) & Chr(10) & [OtherErrors]))

HTH

 

[Ally]

Thank you so much. I'm just writing it all out now on paper with arrows here and there to make sure I really understand it. I've learnt so much already today!

 

[raskew]

You might consider the Switch() function.
To test, create tblErrors, with the four possible
outcomes, looking like this:

ErrorCat......|OtherErrors
ErrorCat not null|
.................|OtherErrors Not null
ErrorCat not null|OtherErrors Not null



This function, placed in a module and called
from the debug window with ? SayIt()

…returns:

? sayit()
BC: ErrorCat not null
AD: OtherErrors Not null
BD: ErrorCat not null
OtherErrors Not null
AC:

Public Function SayIt()
Dim db As DATABASE, rs As Recordset
Dim strHold As String
Dim LF As String

LF = Chr(13) & Chr(10)

Set db = CurrentDb
Set rs = db.OpenRecordset("tblErrors")

Do While Not rs.EOF
   strHold = ""
   strHold = strHold & _
   IIf(IsNull(rs!ErrorCat), "A", "B") & _
   IIf(IsNull(rs!OtherErrors), "C", "D")
   'Line spacing added for readability
   Debug.Print strHold & ": " & Switch( _
   strHold = "AC", "", _
   strHold = "AD", rs!OtherErrors, _
   strHold = "BC", rs!ErrorCat, _
   strHold = "BD", rs!ErrorCat & LF & rs!OtherErrors)
   rs.MoveNext
Loop
rs.Close
db.Close
Set db = Nothing
End Function

 

[Ally]

Thanks Raskew - I've done what you said - but I confess I am lost as to how I use this within my report???