DSum with dates help

[Raysoc]

Need a running sum on weeks, but all I have is a single date column, so what I did was this:

column 1: Week: Format([Date],"yyyy - mm - ww")

column 2: $Amt

column 3: WTD: DSum("$Amt","MyTable",Format([Date],"yyyy - mm - ww") & " <= " & [Week] & "")

and tried this: WTD: DSum("Adj Amt","OneBill",Format([Date],"ww")<=[Week])

This does not work, the example I was following was from an example where there was a month field in the table using numbers to represent the week, i.e. 1,2,3,4 so it made sense, im trying to evaluate two formatted dates and have it total them up.. what am I doing wrong here?

Thanks!


on a side note... does anyone know how to make it pick actual weeks, i.e. Dec27-Jan02 is a week, but in access using the format above for ww 27-31 is a week 54 and 1-2 is week 1, im worried that at the end of the month 31 will be a single week and feb 1-6 will be a week instead of 31-6.

I like that it does this but want to know how to switch between, weeks in a month and weeks in a year.

Thanks!

 

[Murf]

There is no good way to do running sums in a query/table. you could use a linked Excel file to do your calculations.

 

[Raysoc]

Thanks!

I had seen it done here: http://www.datapigtechnologies.com/flashfiles/runningsum.html

So I had hoped it would be possible to change the way the tut does it using number comparisons, and do a date conversion to ww and compare based on those numbers.

Ah well, maybe Ill just use a sheet to link to the table and perform the analysis that way!

 

[Murf]

Try adding single quotes to your dsum function.

DSum("$Amt","MyTable",Format([Date],"yyyy - mm - ww") & " <= '" & [Week] & "'")

 

[Raysoc]

Week: Format([Date],"yyyy - mm - ww") Group By
Adj Amt Sum
WTD: DSum("Adj Amt",Format([Date],"yyyy - mm - ww") & "<=" & [Week] & "'") Expression

Gives this result:

******
<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>Sum Adj by week</caption> <thead> <tr> <th bordercolor="#000000" bgcolor="#c0c0c0">Week</th> <th bordercolor="#000000" bgcolor="#c0c0c0">SumOfAdj Amt</th> <th bordercolor="#000000" bgcolor="#c0c0c0">WTD</th> </tr> </thead> <tbody> <tr valign="TOP"> <td bordercolor="#c0c0c0">2009 - 12 - 53</td> <td bordercolor="#c0c0c0" align="RIGHT">-$212,837.50</td> <td bordercolor="#c0c0c0">#Error</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">2010 - 01 - 1</td> <td bordercolor="#c0c0c0" align="RIGHT">-$94,846.54</td> <td bordercolor="#c0c0c0">#Error</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">2010 - 01 - 2</td> <td bordercolor="#c0c0c0" align="RIGHT">-$5,847,897.37</td> <td bordercolor="#c0c0c0">#Error</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">2010 - 01 - 3</td> <td bordercolor="#c0c0c0" align="RIGHT">-$2,949,875.62</td> <td bordercolor="#c0c0c0">#Error</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">2010 - 01 - 4</td> <td bordercolor="#c0c0c0" align="RIGHT">-$8,938,763.82</td> <td bordercolor="#c0c0c0">#Error</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">2010 - 01 - 5</td> <td bordercolor="#c0c0c0" align="RIGHT">-$3,458,969.07</td> <td bordercolor="#c0c0c0">#Error</td> </tr> <tr valign="TOP"> <td bordercolor="#c0c0c0">2010 - 01 - 6</td> <td bordercolor="#c0c0c0" align="RIGHT">-$4,164.92</td> <td bordercolor="#c0c0c0">#Error</td> </tr> </tbody> <tfoot></tfoot> </table>
Which I dont understand...

 

[Murf]

I think your missing the table name reference as the second parameter.

DSum("Adj Amt","TableName",Format([Date],"yyyy - mm - ww") & "<=" & [Week] & "'")

 

[Raysoc]

Thanks for the suggestion Murf but that doesnt seem to have changed anything just shows #error