Using IsNULL/IsError with IIF funtion.

[Hemi]

I've built an expression query to calculate two fields as:

% NCOTH_NEW ORDER RATE: IIf(IsNull([VOL NCOTH_NEW]/[VOLUME SIEBEL
ORDERS_NEW]),0,[VOL NCOTH_NEW]/[VOLUME SIEBEL ORDERS_NEW])

The issue here is if I replace 0 with text "N/A" the calculations for rest
of the other fields won't display properly. They become something like
8.695652E-02

Can someone pls help on what am I doing wrong here. Leaving 0 isn't an
option as that shows 0.0% for fields where both VOL NCOTH_NEW & VOLUME SIEBEL
ORDERS_NEW has 0 values.

Thanks

 

[Ken Snell]

Try this:

% NCOTH_NEW ORDER RATE: IIf(IsNull([VOL NCOTH_NEW]/[VOLUME SIEBEL
ORDERS_NEW]),"N/A",Format([VOL NCOTH_NEW]/[VOLUME SIEBEL ORDERS_NEW],
"0.00#####"))