Problems with SUM in subform

[Thomas Winkler]

Hi,

i am using AccessXP and have problems with calculating sums in a subform
using parametert from a main form.

The subform contains some records having the fields A and B.
The main form contains the fields C and D.
All the fields contain numeric values.

The following combination works:

=Sum(A*B)
=Parent!C
=Parent!D

The desired combination, that doesn't work:

=Sum(A*B*IIf(Parent!C=1;1;(1+Parent!D))

It seems as if inside the sum-function the fields from the main form cannot
be referred.

Another workaround, that did not work too, was to put new fields, containing
the params from main form, to the subform and to refer to those in the sum
function.

Has anyone an idea how to fix the problem?

Thomas

 

[Steve Schapel]

Thomas,

I am not sure if this is the only problem here, but the parentheses in
your expression are incorrect. You could try like this...
=Sum(A*B*IIf(Parent!C=1;1;1+Parent!D))
or this...
=Sum(A*B)*IIf(Parent!C=1;1;1+Parent!D)

 

[Thomas Winkler]

Hi Steve,

I am not sure if this is the only problem here, but the parentheses in
your expression are incorrect. You could try like this...
=Sum(A*B*IIf(Parent!C=1;1;1+Parent!D))
or this...
=Sum(A*B)*IIf(Parent!C=1;1;1+Parent!D)

thanks for your fast reply. I am sorry, i forgot an important fact. The
values should be rounded before sum.

So the desired expression should be like:

=Sum(Round(A*B*IIf(Parent!C=1;1;1+Parent!D);2))

If I would take the sum like you suggested, I would get _approximately_ the
correct value, but not _exactly_, because of the possible rounding
differences.

So, your suggestion

=Round(Sum(A*B)*IIf(Parent!C=1;1;1+Parent!D);2)

does not fix the problem.

Still any other suggestions?

Could this be another access bug?
Did anyone hear about similar problems using "sum" in fields?

Thomas

 

[Steve Schapel]

Thomas,

So, what actually happens when you use this:
=Sum(Round(A*B*IIf(Parent!C=1;1;1+Parent!D);2))
No data? Incorrect data? Error? Something else?