Need Help with long Nested IIF Expression PLEASE :)

[sandydaly]

I'm trying to add an IF to my already existing expression in my database. I'm trying to add MG to calculate as a new UOM. The expression works and calculates correctly without the addition of the final IF of the "MG" uom. It keeps giving me the error that my expression contains the wrong number of arguments. I tried adding a ) to the end of the expression and that didn't work. I tried deleting one and still no go. Any help would be greatly appreciated.




Amt Base: IIf([Controlled Substance Name]="Morphine Oxycodone Mix",0,IIf([Controlled Substance Name]="Butalbital Codeine Mix",0,IIf([Controlled Substance Name]="Amphetamine WIP FIN",0,IIf([UOM]="EA",[Quantity]*[Number of Tabs or Caps per EA]*[Strength]/1000*[Conversion Factor],IIf([UOM]="KG",[Quantity]*[Percent Active]*1000*[Conversion Factor],[Quantity]*[Percent Active]*[Conversion Factor],IIf([UOM]="MG",[Quantity]/1000*[Percent Active]*[Conversion Factor]))))))

[RuralGuy]

Since there is a limit to nested IIF's (seven I think) and they can be very difficult to read sometimes I use a public SELECT CASE structure procedure in a Standard Module. Easier to read and document.

[ArviLaanemets]

Too many nested IIf's. There is a limit on them.

Workarounds:

1. Design an UDF, which returns needed value;
2. Group IIf's with same outcome. Like

IIf([Controlled Substance Name]="Morphine Oxycodone Mix" OR 
[Controlled Substance Name]="Butalbital Codeine Mix" OR 
[Controlled Substance Name]="Amphetamine WIP FIN",0,
IIf([UOM]="EA",[Quantity]*[Number of Tabs or Caps per EA]*[Strength]/1000*[Conversion Factor],
IIf([UOM]="KG",[Quantity]*[Percent Active]*1000*[Conversion Factor],[Quantity]*[Percent Active]*[Conversion Factor],
IIf([UOM]="MG",[Quantity]/1000*[Percent Active]*[Conversion Factor])))

3. Group IIfs structually. Like

IIf([Controlled Substance Name]="Morphine Oxycodone Mix" OR 
[Controlled Substance Name]="Butalbital Codeine Mix" OR 
[Controlled Substance Name]="Amphetamine WIP FIN",
IIf([Controlled Substance Name]="Morphine Oxycodone Mix",0,
IIF([Controlled Substance Name]="Butalbital Codeine Mix",0,0)),
Iif([UOM]="EA",[Quantity]*[Number of Tabs or Caps per EA]*[Strength]/1000*[Conversion Factor],
IIf([UOM]="KG",[Quantity]*[Percent Active]*1000*[Conversion Factor],[Quantity]*[Percent Active]*[Conversion Factor],[Quantity]/1000*[Percent Active]*[Conversion Factor])))

4. Create a table where all possible combinations and matching return values are defined. Use DLookup to get the value from lookup table.

[sandydaly]

Thanks! I have never used one of those before. I have used modules to run Marcos in Access but not in a query builder. Will it work the same in the query builder or do I have to link the query to a module to make it calculate the correct UOM in the query? All of my queries are tied to these IIF statements with the calculations are reports. Do I type the formula directly into the expression builder? Is this how I would write it?

Select Case performance
Case 1
UOM = “EA”,[Quantity]*[Number of Tabs or Caps per EA]*[Strength]/1000*[Conversion Factor]
Case 2
UOM ="KG",[Quantity]*[Percent Active]*1000*[Conversion Factor],[Quantity]*[Percent Active]*[Conversion Factor]
Case 3
UOM="MG",[Quantity]/1000*[Percent Active]*[Conversion Factor]
Case Is 4
[Controlled Substance Name]="Morphine Oxycodone Mix"=0, [Controlled Substance Name]="Butalbital Codeine Mix"=0, [Controlled Substance Name]="Amphetamine WIP FIN"=0,
Case Else

End Select
End Function

[davegri]

The user defined function suggested by Arvilaanenets would look something like this:
The outer select case selects the drug, and the inner select case deals with the UOM.

Public Function calcAmtBase(argDrug as string, argUOM as string) as double
Select case argDrug
    Case "Morphine Oxycodone Mix", Butalbital Codeine Mix", "Amphetamine WIP FIN"
        Select Case argUOM
            Case "EA"
                 calcAmtBase = [Quantity]*[Number of Tabs or Caps per EA]*[Strength]/1000*[Conversion Factor]
            Case "KG"
                 calcAmtBase = [Quantity]*[Percent Active]*1000*[Conversion Factor]
            Case "MG"
                 calcAmtBase = ......
            Case Else
                 calcAmtBase = 'whatever for no matches
       End Select     'end checking UOM  for drugs above   
    Case              ' any other drugs
     .
     . 
End Select ' end checking for drug names

In your expression, when you want the Amt Base call the function like:

calcAmtBase([Controlled Substance Name],UOM)