question about null data

[Guest]

I have the following formula, which works correctly, in a report I have
created. I use the same formula layout for a number of fields. What this
formula does is take the readings from 3 different phases from an electrical
breaker, and returns the highest. I do this same formula for 12 seperate
breakers, where the data is from 3 seperate tables. This will work good when
there is data for all phases for all breakers, but if any is not present, the
whole reports shows error in place of the data requested.

This is the formula I built using expression builder, for a field named
Text117, on a report (this is duplicated in each text box showing the result
for each of the 12 breakers, each with seperate data:
=IIf([lp52-1IA]>=[lp52-1IB],IIf([lp52-1IA]>=[lp52-1IC],[lp52-1IA],[lp52-1IC]),IIf([lp52-1IB]>=[lp52-1IC],[lp52-1IB],[lp52-1IC]))

lp52-1 is the breaker number, IA would be amps phase A, IB would be amps
phase B, IC would be amps phase C

lp52-1IA is one field, lp52-1IB is another, lp52-1IC is a third field, in a
table.

I want to do the following: If any of the 3 phase amounts are null, the
field shown on the report will show zero (0). If there is data, then I want
to use my formula to generate the amount.


I have looked at the NZ function, and it looks like it would work for me,
but I'm not sure what format to use or where to put it, before my orginal
formula, within it, or after it.

Does anyone have any helpful suggestions to make concerning this approach,
or follow up questions I can try to answer?

TIA

Rick

 

[Marshall Barton]

I have the following formula, which works correctly, in a report I have
created. I use the same formula layout for a number of fields. What this
formula does is take the readings from 3 different phases from an electrical
breaker, and returns the highest. I do this same formula for 12 seperate
breakers, where the data is from 3 seperate tables. This will work good when
there is data for all phases for all breakers, but if any is not present, the
whole reports shows error in place of the data requested.

This is the formula I built using expression builder, for a field named
Text117, on a report (this is duplicated in each text box showing the result
for each of the 12 breakers, each with seperate data:
=IIf([lp52-1IA]>=[lp52-1IB],IIf([lp52-1IA]>=[lp52-1IC],[lp52-1IA],[lp52-1IC]),IIf([lp52-1IB]>=[lp52-1IC],[lp52-1IB],[lp52-1IC]))

lp52-1 is the breaker number, IA would be amps phase A, IB would be amps
phase B, IC would be amps phase C

lp52-1IA is one field, lp52-1IB is another, lp52-1IC is a third field, in a
table.

I want to do the following: If any of the 3 phase amounts are null, the
field shown on the report will show zero (0). If there is data, then I want
to use my formula to generate the amount.


I have looked at the NZ function, and it looks like it would work for me,
but I'm not sure what format to use or where to put it, before my orginal
formula, within it, or after it.

You can use the Nz function on every instance of a field.

=IIf(Nz([lp52-1IA],0)>=Nz([lp52-1IB],0),IIf(Nz([lp52-1IA],0)>=...

Or, it might be cleaner to use it in the report's record
source query instead of so many times in that expression.