Concatenating Nested IIfs! (1 Viewer)

[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])," "))))

 

[Pat Hartman]

You need one more operand -

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

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.


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

Just a hint from an old dinosaur programmer - make an effort to use possitive conditional texts rather than negative ones. They are easier to follow logically. Only use negative tests if the possitive one would result in having to test too many values.

 

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

 

[Pat Hartman]

Compound conditions are conditional statements that are comprised of more than one part. The parts are connected by a relational operator such as AND, OR, XOR. A simple example is:

A = 3 Or A = 8

Compound conditions become complex when they include different relational operators or the negation operand - NOT and usually need parentheses surrounding various clauses to make sure that they are interpreted as you intend.

A = 3 Or A = 8 AND B = 5

The above statement is an example of a potential problem. Most people would interpret it as (A = 3 or 8) And B = 5 so they think that A = 3 and B = 6 would test false. However, that is not how your computer will interpret the statement. All computer languages use what is called "order of presidence" when interpreting compound conditions (this also applies to mathmatical calculations). AND operations take presidende over OR operations so they are evaluated first. That means that the condition "A=8 AND B=5" will be evaluated first and that result will be Or'd with the first condition "A=3". So really the condition is interpreted as -
A = 3 Or (A = 8 AND B = 5)
Which means that if A is 3, the value of B is irrelevant. To force the statement to be evaluated as most people would read it, you need to put in your own parentheses to override the default order of presidence -
(A = 3 Or A = 8) AND B = 5
This statement says that B must always be 5 and A can be either 3 or 8.

The above is simply the tip of the iceberg. When using compound conditions, especially complex ones, it is imperitive that you understand boolean logic and order of persidence.

 

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