how to use a calculated field to get the average of 4 text fields

[jbp]

I'm new to Access, so this may seem absurd...



I have 4 texts fields [DEVICE INSTALL] , [INSTRUMENT STAND], [BRACKETS RACEWAY SUPPORTS], [TUBING CAPILLARY]
all can be either a percentage from 1-100 or "N/A" except [DEVICE INSTALL].

I would like to calculate the [percentage complete]

my calculation in the field is as follows:
([DEVICE INSTALL]+
IIf([INSTRUMENT STAND]="N/A",100,[INSTRUMENT STAND])+
IIf([TUBING CAPILLARY]="N/A",100,[TUBING CAPILLARY])+
IIf([BRACKETS RACEWAY SUPPORTS]="N/A",100,[BRACKETS RACEWAY SUPPORTS]))
/4

this seems to work to a degree. but for some reason when the [INSTRUMENT STAND] value is not "N/A" the percentage calculated is a wacky value much higher than 100.

Why is this and how might I fix it?
and if anyone might be interested, how could I possibly change my division #?

[orange]

Sounds like you may need to review your use of text fields. Seems you may want to try numeric fields.

[jbp]

Sounds like you may need to review your use of text fields. Seems you may want to try numeric fields.

I considered that but the problem is I need to be able to enter an "N/A" text value if either of the fields do not apply. And a Numeric Field won't allow it.

What to Do...

[orange]

You could use a negative value, check for such and display "N/A" for presentation.

[jbp]

found a solution:

(int([DEVICE INSTALL])+
IIf([INSTRUMENT STAND]="N/A",100,int([INSTRUMENT STAND]))+
IIf([TUBING CAPILLARY]="N/A",100,int([TUBING CAPILLARY]))+
IIf([BRACKETS RACEWAY SUPPORTS]="N/A",100,int([BRACKETS RACEWAY SUPPORTS])))
/4