G
Guest
I have been able to use the following code to display results on a report. I
am trying to use this same code in a query but I keep coming up with errors
including "Division by zero". Some of the fields will be null and cannot have
a "0" for the value. The result of this calculation is an average.
=(Nz([Hour1],0)+Nz([Hour2],0)+Nz([Hour3],0)+Nz([Hour4],0)+Nz([Hour5],0)+Nz([Hour6],0)+Nz([Hour7],0)+Nz([Hour8],0)+Nz([Hour9],0)+Nz([Hour10],0)+Nz([Hour11],0)+Nz([Hour12],0)+[Factored_DT])/(IIf([Hour1]
Is Null,0,1)+IIf([Hour2] Is Null,0,1)+IIf([Hour3] Is Null,0,1)+IIf([Hour4] Is
Null,0,1)+IIf([Hour5] Is Null,0,1)+IIf([Hour6] Is Null,0,1)+IIf([Hour7] Is
Null,0,1)+IIf([Hour8] Is Null,0,1)+IIf([Hour9] Is Null,0,1)+IIf([Hour10] Is
Null,0,1)+IIf([Hour11] Is Null,0,1)+IIf([Hour12] Is Null,0,1))
I also realize this is repetative data and should be in a relating table
because each Hour is it's own field (1-12). Any help or advice on any of the
above would be greatly appreciated.
am trying to use this same code in a query but I keep coming up with errors
including "Division by zero". Some of the fields will be null and cannot have
a "0" for the value. The result of this calculation is an average.
=(Nz([Hour1],0)+Nz([Hour2],0)+Nz([Hour3],0)+Nz([Hour4],0)+Nz([Hour5],0)+Nz([Hour6],0)+Nz([Hour7],0)+Nz([Hour8],0)+Nz([Hour9],0)+Nz([Hour10],0)+Nz([Hour11],0)+Nz([Hour12],0)+[Factored_DT])/(IIf([Hour1]
Is Null,0,1)+IIf([Hour2] Is Null,0,1)+IIf([Hour3] Is Null,0,1)+IIf([Hour4] Is
Null,0,1)+IIf([Hour5] Is Null,0,1)+IIf([Hour6] Is Null,0,1)+IIf([Hour7] Is
Null,0,1)+IIf([Hour8] Is Null,0,1)+IIf([Hour9] Is Null,0,1)+IIf([Hour10] Is
Null,0,1)+IIf([Hour11] Is Null,0,1)+IIf([Hour12] Is Null,0,1))
I also realize this is repetative data and should be in a relating table
because each Hour is it's own field (1-12). Any help or advice on any of the
above would be greatly appreciated.