My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 DSUM confusion
|
|
 Sep 2 2006, 08:58 AM
Post
#1
|
|
|
UtterAccess Addict Posts: 231  |
I have a query with the following fields:
Month, Year, YearMonth, AmtW, AmtD, TotD, TotW The YearMonth field just concatenates year, and then month. The TotD and TotW fields are DSUMS, but I can't get them to work properly. I want the DSUMS to sum all values in AmtW or AmtD where YearMonth is less than or equal to what the current row is. For example: Month..........Year............YearMonth.........AmtW.........AmtD..........TotD ............TotW April...........2005.............200504.............($5)...........$20.......... ...$20.............($5) June..........2005.............200506.............$0..............$100.......... $120............($5) Sept...........2006............200609.............($45)...........$0............ .$120............($50) I am trying to use the YearMonth for the condition in DSUM, but I can't get it to work right. Any ideas? Thanks. |
 |
 
|
|
Sep 2 2006, 12:55 PM
Post
#2
|
|
|
UtterAccess VIP Posts: 5,200 From: Denver, Colorado [USA] |
TotW: DSUM("AmtW","tableorqueryname","YearMonth<'" & [YearMonth] & "'")
Note that YEARMONTH needs to be a field or concatenated expression in the underlying table or query. |
|
|
|
|
Sep 2 2006, 01:21 PM
Post
#3
|
|
|
UtterAccess Addict Posts: 231 |
That works great. Thanks!
|
|
|
|
|
Sep 2 2006, 01:49 PM
Post
#4
|
|
|
UtterAccess VIP Posts: 5,200 From: Denver, Colorado [USA] |
Glad to help. If you want to do less than or equal to, we should change that to:
TotW: DSUM("AmtW","tableorqueryname","YearMonth<='" & [YearMonth] & "'") |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 19th June 2013 - 08:46 PM |