Improving efficiency of IIf

[tezread]

I have a custom field in a query

Outcomereport: IIf([Outcome] Is Null,"No outcome",IIf([Outcome]="GP","GP",IIf([Outcome]="Cardioversion","Cardioversion",IIf([Outcome]="Cardiology","Cardiology",0))))

but is there a more efficient way doing this so it says if the outcome is null "no outcome" ........ otherwise keep whatever else is recorded as an outcome

As you can see its ok as i only have 4 outcomes but the above code would be a nightmare to type if there were lots of outcomes!

Cheers in anticipation guys n gals

 

[DCrake]

Nz(Outcome,"No Outcome")

 

[Simon_MT]

Or it would seem:

IIf([Outcome] Is Null,"No Outcome",[Outcome])

Simon

 

[vbaInet]

I would be more inclined to use DCrake's method or this:

IIF(IsNull([Outcome]), "No outcome", ...)

Is Null is used to check objects in vba and in criteria in queries.

 

[boblarson]

Or it would seem:

IIf([Outcome] Is Null,"No Outcome",[Outcome])

Simon

David's : Nz(Outcome,"No Outcome")

is the most efficient of the checks.

 

[gemma-the-husky]

dont know if switch function would be a better way of examining the alternates

i try to avoid nested iif's though

i would tend to have another table, maybe, if there were alternate outcomes.

makes it easier when you want to change values/add values, and also prevents mistyping - although i presume you use hard-coded combo boxes etc

 

[boblarson]

dont know if switch function would be a better way of examining the alternates

As we are only concerned with NULLS here, the

Nz(Outcome,"No Outcome")

would be the best since it will only need to evaluate if null and will provide the output if null but if not then it uses the field straightup. I believe that should entail the fewest checks required on the data.

 

[ChrisO]

Efficiency can mean many things…

Shortest code; very often the shorter the code the slower it is.
Fastest code; very often meaningless in reality and needs to be timed.
More general code; almost always slower than specific code but the difference is often meaningless in reality.

The following will handle a Null or a ZLS value but would need timing against a test just for Null.

    ' In a Query...
    OutcomeField: IIf(Len([Outcome]),[Outcome],"No Outcome")

    
    ' In VBA...
    With CurrentDb.OpenRecordset("Table1")
        Do Until .EOF
            MsgBox IIf(Len(!Outcome), !Outcome, "No Outcome")
            .MoveNext
        Loop
    End With

Len(X) is the fastest test for Null or ZLS but it is measured in microseconds.
IIF(Blah) processes both the True and the False parts so could slow it down.
Testing for both Null and ZLS can be of use; no real point in rushing to a high speed crash.
Ability to edit code, with some ease, is an important factor.

HTH

Chris.

 

[gemma-the-husky]

i modfied my post since - see above - i would probably use a lookup table, tbh.

 

[Simon_MT]

An iif has true false and yes a table would be more proficient. If the control is numeric I sometimes use IsNumeric but again that is probably less effiicent than Nz.

Simon

 

[vbaInet]

We're rather up late aren't we Simon?

One might argue that there's the cost of processing from a recordset - opening, reading, closing and setting free (if you so desire). An IIF() function in a query would certainly run faster than the recordset option. I tend to use that as last resort. That said, if those values need to be normalized then yes I would concur with reading from a table.

 

[ChrisO]

vbaInet.

Since the recordset was posted by me I try to answer your point.

I was not suggesting that a recordset would be faster but simply giving both syntax.

This question was asked in a query forum and therefore the query version I posted would be my choice.

When testing for speed I am quite sure the recordset version I posted would be the slower of the two.

But the speed would have nothing to do with the recordset but more to do with clicking on the OK button of the MsgBox.


Chris.

 

[vbaInet]

Roger that!

Haha! I wonder how many clicks it would require

 

[ChrisO]

I would think that, with a large recordset, we would be in Cardiology before we found out.