Protecting against a null value in a calculated field

[Melissa]

Hi there

I have created a report for a payslip, and on this report I have a field
"RatePerShift", which takes the "TotalDueNormal Grand Total Sum" field ,
divided by "NoOfShiftsNormal Grand Total Sum" to get what the RatePerShift
is. This calculation works perfectly, however, if the employee did not work
any Normal shifts, the RatePerShift field goes to "Num".

I have tried the following to try and put the "Num" to a zero, but It isnt
working, please could someone assist me......

=IIf(IsNull([TotalDueNormal Grand Total Sum]/[NoOfShiftsNormal Grand Total
Sum]),"0",[TotalDueNormal Grand Total Sum]/[NoOfShiftsNormal Grand Total
Sum])

Thank you

Melissa

 

[Golfinray]

Try nz instead of isnull.

 

[ghetto_banjo]

When the person doesnt work any shifts, is that value in your table
being stored as null, or as zero?? Check to see if its actually a
zero, and change your iif statement accordingly to check for zero
there.

 

[Melissa]

Hi

it is being stored as a zero.. I have tested something else just to see,
and when I change "/" to "+", then a zero is displayed on the report as it
should. but the minute I change it to "/", it goes to #Num?

 

[ghetto_banjo]

it is showing the #Num, because it cannot divide by zero.
([totalDuenormal Grand Total Sum] / 0) is not null, it is an illegal
operation.

you want your iif statement to be:

iif([NoOfShiftsNormal Grand Total Sum] = 0, 0, [TotalDueNormal Grand
Total Sum] / [NoOfShifts Normal Grand Total Sum])


so in other words: if number of shifts is zero, then show zero,
otherwise show the rate