Calculation

R

Rene Hernandez

What is the translation of the following into an expression

Add [a]++[c], if [a] does not have a value, then only add and [c], if
does not have a value, then only add [a] and [c], etc...

These are all text values. Ex: NA, 1, 2, 3

Thanks.
 
J

Jerry Whittle

Totals: IIf(NZ([a], 0) = "NA", 0, [a]) + IIf(NZ(, 0) = "NA", 0, ) +
IIf(NZ([c], 0) = "NA", 0, [c])

One point of concern is non-numeric characters. I only trapped "NA". If you
have any other, such as "N/A" or "NONE", you'll have to trap them.

Also "NA" is a value. Having numerical and text characters in the same field
AND wanting to do math on the numerical characters is a sure sign that the
table is not normalized properly.
 
J

John W. Vinson

What is the translation of the following into an expression

Add [a]++[c], if [a] does not have a value, then only add and [c], if
does not have a value, then only add [a] and [c], etc...

These are all text values. Ex: NA, 1, 2, 3
Click to expand...


You can use the NZ() function to convert null to a text string zero, and Val()
to convert "2" to a numeric 2 for addition:

Val(NZ([a], "0")) + Val(NZ(, "0")) + Val(NZ([c], "0"))


John W. Vinson [MVP]
 
Top