DSum where data is from previous week only

[doodles]

Hi,



I have a table with account number, amount paid and paid date (TBL_DAILY_PAYMENTS).

The first batch of records are dated 06/01/2011 and the latest batch is currently 25/01/2011 (daily file with anywhere from 1 record to 1000+).

I'm trying to bring back the total paid for each account number in the last week as well as YTD in two columns of my account summary table (TBL-DASHBOARD).

I've used the below update query for YTD and this seems to be working fine.

DSum("[AMOUNT PAID]","TBL_DAILY_PAYMENTS","[ACCOUNT NUMBER] = '" & TBL_Dashboard![Account Number] & "' AND Year([PAID_DATE])= " & Year(Date()))

My issue is with summing amounts paid in the last week and bringing those into the Dashboard table.

I was under the understanding that the below would work...

DSum("[AMOUNT PAID]","TBL_DAILY_PAYMENTS","[ACCOUNT NUMBER] = '" & TBL_Dashboard![Account Number] & "' AND [PAID_DATE]>= '" & DateAdd("d",-7,Date()) & "'")

The idea is to run the update query every Monday and retrieve the latest YTD total as well as the total of amounts paid in the previous week.

Could anybody please help me to fix my query as the DateAdd function is not working as I have been told it will and is bringing back exactly the same as the YTD total (YTD total has been checked and the updated records for this field are correct)???

Please bear in mind that I am relatively new to Access and just want an update query...no VB or SQL please!
Thanks very much!!!

[jzwp11]

DSum("[AMOUNT PAID]","TBL_DAILY_PAYMENTS","[ACCOUNT NUMBER] = '" & TBL_Dashboard![Account Number] & "' AND [PAID_DATE]>= '" & DateAdd("d",-7,Date()) & "'")

Assuming that your [PAID_DATE] field is a date/time datatype then you have to enclose the dateadd() with # signs not single quotes. Single quotes are used for text fields, # signs are used for date/time fields. No delimiters are need for numeric fields such as the Year as in the other DSum() you mentioned.

So the correct syntax is as follows

DSum("[AMOUNT PAID]","TBL_DAILY_PAYMENTS","[ACCOUNT NUMBER] = '" & TBL_Dashboard![Account Number] & "' AND [PAID_DATE]>= #" & DateAdd("d",-7,Date()) & "#")

[doodles]

Thank you so much...it is now working as required.

Will I need to add # to the below code as well to ensure it work's correctly?

DSum("[AMOUNT PAID]","TBL_DAILY_PAYMENTS","[ACCOUNT NUMBER] = '" & TBL_Dashboard![Account Number] & "' AND Year([PAID_DATE])= " & Year(Date()))

Thanks so much again :0)

[jzwp11]

Will I need to add # to the below code as well to ensure it work's correctly?

No, since you are using the year() function, the function returns a numeric value which needs no delimiters.