If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DSUM Report
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. |
#2
|
|||
|
|||
DSUM Report
Does qryDELIVERYVOUCHER have any criteria?
Have you tried a simple expression like: =Sum(Abs(PaymentMethod=1) * PaymentAmount) -- Duane Hookom MS Access MVP -- "Ray Todd Jr" wrote in message ... 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. |
#3
|
|||
|
|||
DSUM Report
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. "Duane Hookom" wrote: Does qryDELIVERYVOUCHER have any criteria? Have you tried a simple expression like: =Sum(Abs(PaymentMethod=1) * PaymentAmount) -- Duane Hookom MS Access MVP -- "Ray Todd Jr" wrote in message ... 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. |
#4
|
|||
|
|||
DSUM Report
=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 -- "Ray Todd Jr" wrote in message ... 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. "Duane Hookom" wrote: Does qryDELIVERYVOUCHER have any criteria? Have you tried a simple expression like: =Sum(Abs(PaymentMethod=1) * PaymentAmount) -- Duane Hookom MS Access MVP -- "Ray Todd Jr" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
subreport not displaying in main report | JohnLute | Setting Up & Running Reports | 15 | November 17th, 2005 04:02 PM |
To Sharkbyte and all: Calculate a total values in group level | Ally | General Discussion | 6 | June 13th, 2005 08:16 PM |
DSum in a report | neenmarie | Setting Up & Running Reports | 0 | April 28th, 2005 03:38 PM |
Help!! I'm running around in circles! | CathyA | New Users | 19 | December 12th, 2004 07:50 PM |
Save Report With CreateReport Coding Issue | Jeff Conrad | Setting Up & Running Reports | 8 | July 12th, 2004 08:39 AM |