Problems with SUM in subform

T

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
 
S

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)
 
T

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)
Click to expand...

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
 
S

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?
 
T

Thomas Winkler

Hi,
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?
Click to expand...

the textbox that contains this expression displays #Error.

When i replace Parent!C and Parent!D with constants (so that no access to
the main form is required) the correct value is displayed.

Thomas.
 
S

Steve Schapel

Thomas,

Thanks for the further information. Just shooting in the dark here...
Does it make any difference to use []s, like this...
=Sum([A]**IIf([Parent]![C]=1;1;1+[Parent]![D]))
 
T

Thomas Winkler

Hi Steve,

thanks for your assistance. I could not fix the problem, but i found a
workaround.

It seems as if the problem is caused by access' country related number
conversion.

In US you write "1.66", in Germany we write "1,66" - but in memory likewise
"1.66" is stored. Therefore parameters for functions in forms etc. were
separated by ";" and not by ",". The names of functions are "germanized"
too.

So "=Sum(Round([A] * * IIf(Parent!C = 1, 1, 1+Parent!D), 2))" in english
would be
"=Summe(Runden([A] * * Wenn(Parent!C = 1; 1; 1+Parent!D); 2))" in
german.

Internally access converts this expressions to US-form.

When i now add 1+Parent!D the result is "1,66". After conversion to
us-standard the expression looks like

"=Sum(Round([A] * * IIf(False, 1, 1,66), 2))"
^^^^
But iif() only has 3 params.

The workaround is now to use DSum() in the main form to get the correct
value. This works because the expression is passed to DSum() as String.
There the country conversion works correctly because i can mix up german and
english syntax.

The DSum() expression in the main form now looks like:

=DomSumme("Round([A] * * CSng(" & Replace(Wenn([C]=1;1;1+[D]);",";".") &
"), 2)";"[myTable]";"[ID] = " & [ID])

DomSumme == DSum

Thanks for your help.

Thomas
 
S

Steve Schapel

Thomas,

Thanks for the feedback as to what you have found about the root of the
problem.

You have found somethiong that works for you, for now. But I would like
to run this past some of the European MVPs for their comments.
 
Top