DSUM Report

[Guest]

I am attempting to get the following DSUM statement to work, however, all I
get is #Error.

The record source for the report is set to qryDELIVERYVOUCHER. The query
generates the following record source [PaymentAmount], [PaymentMethod]

On the report, I have a text box. The control source of the textbox has the
following:

=DSUM("PaymentAmount", "qryDELIVERYVOUCHER", "PaymentMethod=1")

As stated above, when the report is executed, I get the error: #Error.

What am I not doing?

Thanks,

Ray.

 

[Duane Hookom]

Does qryDELIVERYVOUCHER have any criteria?
Have you tried a simple expression like:

=Sum(Abs(PaymentMethod=1) * PaymentAmount)

 

[Guest]

Hello Duane:

I entered the formula as you wrote it and it works. This leads to the
next question. I see the formula, but I don't understand it. I mean, I
understand what the sum function does, however, I don't understand how the
Abs function made the difference and am curious as to why?

I understand (or at least think I do) the Abs function. It returns the
absolute value of a number. The absolute number of -1=1, etc.

How does multiplying the ABS number of (PaymentMethod) by the PaymentAmount
= the Correct Sum.

I realize I may be asking a rather lengthy question, if so, please ignore.
I'm just trying to understand the syntax.

Thanks,

Ray.

Does qryDELIVERYVOUCHER have any criteria?
Have you tried a simple expression like:

=Sum(Abs(PaymentMethod=1) * PaymentAmount)

--
Duane Hookom
MS Access MVP
--

I am attempting to get the following DSUM statement to work, however, all I
get is #Error.

The record source for the report is set to qryDELIVERYVOUCHER. The query
generates the following record source [PaymentAmount], [PaymentMethod]

On the report, I have a text box. The control source of the textbox has
the
following:

=DSUM("PaymentAmount", "qryDELIVERYVOUCHER", "PaymentMethod=1")

As stated above, when the report is executed, I get the error: #Error.

What am I not doing?

Thanks,

Ray.

 

[Duane Hookom]

=Sum(Abs(PaymentMethod=1) * PaymentAmount)

Apply the expression/calculation record by record. If a record has a
(PaymentMethod of 1) the calculation is the PaymentAmount. If the
(PaymentAmount is not 1) the calculation is 0. You then only need to Sum()
the calculation.

This is many times more efficient than using DSum(). Plus, if you apply a
where clause when opening the report, the DSum() ignores the filter. Using
=Sum(Abs()*...) calculates based on the records displayed in the report.

--
Duane Hookom
MS Access MVP
--

Hello Duane:

I entered the formula as you wrote it and it works. This leads to the
next question. I see the formula, but I don't understand it. I mean, I
understand what the sum function does, however, I don't understand how the
Abs function made the difference and am curious as to why?

I understand (or at least think I do) the Abs function. It returns the
absolute value of a number. The absolute number of -1=1, etc.

How does multiplying the ABS number of (PaymentMethod) by the
PaymentAmount
= the Correct Sum.

I realize I may be asking a rather lengthy question, if so, please ignore.
I'm just trying to understand the syntax.

Thanks,

Ray.

Does qryDELIVERYVOUCHER have any criteria?
Have you tried a simple expression like:

=Sum(Abs(PaymentMethod=1) * PaymentAmount)

--
Duane Hookom
MS Access MVP
--

I am attempting to get the following DSUM statement to work, however,
all I
get is #Error.

The record source for the report is set to qryDELIVERYVOUCHER. The
query
generates the following record source [PaymentAmount], [PaymentMethod]

On the report, I have a text box. The control source of the textbox
has
the
following:

=DSUM("PaymentAmount", "qryDELIVERYVOUCHER", "PaymentMethod=1")

As stated above, when the report is executed, I get the error: #Error.

What am I not doing?

Thanks,

Ray.