DSum with dates help

Thanks:  0
Likes:  0

# Thread: DSum with dates help

1. ## DSum with dates help

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!

2. ## Re: DSum with dates help

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

3. ## Re: DSum with dates help

Thanks!

I had seen it done here: http://www.datapigtechnologies.com/f...unningsum.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!

4. ## Re: DSum with dates help

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

5. ## Re: DSum with dates help

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

Gives this result:

******
2009 - 12 - 53 -\$212,837.50 #Error
2010 - 01 - 1 -\$94,846.54 #Error
2010 - 01 - 2 -\$5,847,897.37 #Error
2010 - 01 - 3 -\$2,949,875.62 #Error
2010 - 01 - 4 -\$8,938,763.82 #Error
2010 - 01 - 5 -\$3,458,969.07 #Error
2010 - 01 - 6 -\$4,164.92 #Error

Which I dont understand...

6. ## Re: DSum with dates help

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

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

7. ## Re: DSum with dates help

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•