DSUM - Computing Runnig Totals Question

  • Thread starter Thread starter mcl
  • Start date Start date
M

mcl

Seems to me this should be a lot easier than it seems to be.

OK,
With a table name of "table".
Parameter name of "Amount"
I want to to produce a running total based on a date field I just called
"date".

RunTotal: dsum("amount","table","date")


All I get is a singe grand total repeated for every record.

How do I get this dsum to do what i want it to do?
 
Assuming your recordset is sorted by date, try

RunTotal: DSum("amount", "table", "[date]" <= [date])

Note that you should rename your field from date (and your table from
table). date and table are both reserved words, and you should never use
reserved words for your own purposes. For a comprehensive list of names to
avoid (as well as a link to a free utility to check your application for
compliance), check what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html
 
Small correction to Douglas Steele's suggestion.

RunTotal: DSum("amount", "table", "[date] <=#" & [date] & "#")

That does assume that your operating system is set to use mm-dd-yyyy as
the date format.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thanks, John: I meant to come back and correct that. However, since not
everone uses mm/dd/yyyy as their Short Date format, I'd recommend

RunTotal: DSum("amount", "table", "[date] <=" & Format([date],
"\#yyyy\-mm\-dd\#"))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John Spencer said:
Small correction to Douglas Steele's suggestion.

RunTotal: DSum("amount", "table", "[date] <=#" & [date] & "#")

That does assume that your operating system is set to use mm-dd-yyyy as
the date format.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Assuming your recordset is sorted by date, try

RunTotal: DSum("amount", "table", "[date]" <= [date])

Note that you should rename your field from date (and your table from
table). date and table are both reserved words, and you should never use
reserved words for your own purposes. For a comprehensive list of names
to avoid (as well as a link to a free utility to check your application
for compliance), check what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html
Click to expand...
Click to expand...
 
Thanks, that worked.

As for the use of "reserved words", I once saw a list of reserved words as
was amazed at the the number of "reserved words" I've used in the past which
have never caused me problems.


John Spencer said:
Small correction to Douglas Steele's suggestion.

RunTotal: DSum("amount", "table", "[date] <=#" & [date] & "#")

That does assume that your operating system is set to use mm-dd-yyyy as
the date format.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Assuming your recordset is sorted by date, try

RunTotal: DSum("amount", "table", "[date]" <= [date])

Note that you should rename your field from date (and your table from
table). date and table are both reserved words, and you should never use
reserved words for your own purposes. For a comprehensive list of names
to avoid (as well as a link to a free utility to check your application
for compliance), check what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html
Click to expand...
Click to expand...
 
Back
Top