Results 1 to 7 of 7

DSum with dates help

This is a discussion on DSum with dates help within the Microsoft Access forums, part of the Question Forums category; Need a running sum on weeks, but all I have is a single date column, so what I did was ...

  1. #1
    Board Regular
    Join Date
    Feb 2009
    Posts
    227

    Default 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!
    Last edited by Raysoc; Jan 27th, 2010 at 12:58 PM.

  2. #2
    New Member
    Join Date
    Apr 2009
    Posts
    25

    Default 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. #3
    Board Regular
    Join Date
    Feb 2009
    Posts
    227

    Default 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. #4
    New Member
    Join Date
    Apr 2009
    Posts
    25

    Default Re: DSum with dates help

    Try adding single quotes to your dsum function.

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

  5. #5
    Board Regular
    Join Date
    Feb 2009
    Posts
    227

    Default Re: DSum with dates help

    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:

    ******
    Sum Adj by week
    Week SumOfAdj Amt WTD
    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. #6
    New Member
    Join Date
    Apr 2009
    Posts
    25

    Default 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. #7
    Board Regular
    Join Date
    Feb 2009
    Posts
    227

    Default Re: DSum with dates help

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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com