Calculation problem

[Simon Wong]

For a record I have upto 5 pairs of positive numbers for 10 fields, namely A,
B, C, D, E and V, W, X, Y, Z.
A and V come to a pair such that both A and V must be a positive number > 0.
In case there is more than one pair, the 2nd pair will use B and W
If there are three pairs, then AV, BW and XY will be entered the relevant
positive number. The other fields will becomes "0".
They always comes in pair such that if:
A>0, then V must be >0
A=0, then V must be 0 (which is not possible because there is at least one
pair)
B>0, then W must be >0
B=0, then W must be 0
and so on

Then I create a query field and perform the following calculation:-

(V+W+X+Y+Z) / ( (A/V) + (B/W) + (C/X) + (D/Y) + (E/Z) )

It happens that if any of B/W or C/X or D/Y or E/Z is "0/0", it returns an
Error. Only when 5 pairs all have data, it give a correct calculation.

I know how to use IIf function but it only good for setting one criteria and
in above case it appears there are four different situctions so that the IIf
function cannot manage.

Wish someone can tell me how to deal with my situation.

Best regards
Simon Wong

 

[Allen Browne]

To avoid division by zero, test each of the parts of the denominator like
this:
... / ([A]/[V] + IIf([W]=0, 0, /[W]) + IIf([X]=0, 0, [C]/[X]) + ...)

Actually, there is probably a better way to set this up, so that you have a
related table so there can be between 1 and 5 (or more) records for each one
in the main table.

 

[tina]

try

(V+W+X+Y+Z) / ((A/V) + IIf(B = 0, 0, (B/W)) + IIf(C = 0, 0, (C/X)) + IIf(D =
0, 0, (D/Y)) + IIf(E = 0, 0, (E/Z)))

or you could put the entire calculation into a public function, as

Public Function isResult(ByVal dblV As Double, ByVal dblW As Double, _
ByVal dblX As Double, ByVal dblY As Double, ByVal dblZ As Double, _
ByVal dblA As Double, ByVal dblB As Double, ByVal dblC As Double, _
ByVal dblD As Double, ByVal dblE As Double) As Double

If dblB > 0 Then
dblB = dblB / dblW
End If

If dblC > 0 Then
dblC = dblC / dblX
End If

If dblD > 0 Then
dblD = dblD / dblY
End If

If dblE > 0 Then
dblE = dblE / dblZ
End If

isResult = (dblV + dblW + dblX + dblY + dblZ) / ((dblA / dblV) + dblB +
dblC + dblD + dblE)

End Function

and use the function in your calculated field, as

IsResult([V],[W],[X],[Y],[Z],[A],,[C],[D],[E])

i don't know which solution would run more efficiently; you'd just have to
test them and see if there is a noticeable difference in performance between
the two.

hth