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!
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: