DSum() producing 'strange' results

[Guest]

Im using Access/Windows 2000

Im trying to use DSum() in the footer of a continuous form I want to sum the
[dblContributionAmount] field when the [dteDateCeased] field is empty to give
me a total of contributions currrently being paid. the form picks up its data
from a table called "staging_tblRegAVC"

I have the following formula in a text box in the form footer
=DSum([dblContributionAmount],"staging_tblRegAVC","[dteDateCeased] is null")

The results I get are 'strange' (for want of a better word) it seems that
access is totalling the [dblContributionAmount] of the first record multiple
times depending on whether [dteDateCeased] is null or not...Im not sure if
that makes sense but maybe the following examples will clarify....

eg#1
[Record No] [dblContributionAmount] [dteDateCeased]
1 30.00
2 20.00
3 10.00
DSum() total = 90.00

eg#2
[Record No] [dblContributionAmount] [dteDateCeased]
1 20.00
2 30.00
3 10.00
DSum() total = 60.00

eg#3
[Record No] [dblContributionAmount] [dteDateCeased]
1 10.00 1/1/2005
2 30.00 1/1/2005
3 20.00
DSum() total = 10.00

Thanks in advance for any help anyone can offer

 

[Graeme Richardson]

Hi, try enclosing the name of the field that you're summing in quotes.
I'm guessing, but you may have a control on the form or a datasource
containg the field "dblContributionAmount" and that the DSum() functioin is
mapping to it.

=DSum("[dblContributionAmount]","staging_tblRegAVC","[dteDateCeased] is
null")

HTH, Graeme.

 

[Guest]

Thanks Graeme, that worked a treat