couple of field calculations... (1 Viewer)

[melanemac]

I need to create some calculations.

I have some fields that are calculating a number (see below) * a percentage. Some records may not have the number, so they are coming out as #Error in that calculation, which is fine to me (this is a multi-step query).

Field1 Field2 Field3 Field4
10 5 10 10
4 3 3

As part of the second step, I want to then take an average of the numbers in Fields1-4 and create a new Field. How do I do this? I currently have made a table with the final step of multiplication calculations above, which eliminates the #Error values. However, I can't seem to take an average of the fields if one of the fields is blank.

Any help would be much appreciated.
Melane

 

[Liv Manto]

use nz

nz(mynullfield, 0)

Sets the value to zero in case your field is null.

 

[melanemac]

Thanks

Thanks.
That worked for the null field, but I would like to try and take the "Make Table" out of the equation.

Is it possible to do a count on the fields if one of the fields is #ERROR?
I have tried the ISNUMERIC and ISERROR and I can't seem to get them to work.

My queries start as follows:
1. Pull Salary
SM1 25A: IIf([total bm file - step 1]!pay_grade>"12",([Total BM FILE - STEP 1]![SM1 25]*[CREATE ACTUAL AGING FACTOR]![Actual Aging Factor]),([Total BM FILE - STEP 1]![SM1 25]*[CREATE ACTUAL AGING FACTOR]![Actual Aging Factor]*[geographic adjustments]![adjust percent]))
The problem lies in that if one of the fields that is linked to this query is empty, then the calculation comes out as #ERROR.

2. Count SM1 25A, SM2 25A, SM3 25A, SM4 25A
The only way I could find around the #ERROR issue, was to create a Make Table from 2 above and then the fields are null.

Any suggestions?
Thanks!
Melane