Calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rene Hernandez - 30 Jul 2007 18:26 GMT

What is the translation of the following into an expression

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

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

Thanks.

Signature

Rene Lazaro

Reply to this Message

Jerry Whittle - 30 Jul 2007 19:42 GMT

Totals: IIf(NZ([a], 0) = "NA", 0, [a]) + IIf(NZ([b], 0) = "NA", 0, [b]) +
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.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> What is the translation of the following into an expression
>
[quoted text clipped - 4 lines]
>
> Thanks.

Reply to this Message

John W. Vinson - 30 Jul 2007 20:01 GMT

>What is the translation of the following into an expression
>
>Add [a]+[b]+[c], if [a] does not have a value, then only add [b] and [c], if
>[b] does not have a value, then only add [a] and [c], etc...
>
>These are all text values. Ex: NA, 1, 2, 3

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([b], "0")) + Val(NZ([c], "0"))

John W. Vinson [MVP]

Reply to this Message

Gina Whipp - 31 Jul 2007 03:38 GMT

Try: Nz([a],0)+Nz([b],0)+Nz([c],0)

Signature

Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

> What is the translation of the following into an expression
>
[quoted text clipped - 5 lines]
>
> Thanks.

Reply to this Message