Totaling between dates

[Hecronis]

So I am not very good at writing criteria. I have a column where I would like to total the number of payments made by a client for the previous week. Here is what I have so far.

DSum("Payment_RentTransaction","TblRent_Report","ID_Number=" & [QryClient_CurrentResidents]![ID_Number])

That's just giving me the total payments made by each person. How do I narrow it down to only payments from Monday through Sunday of the previous week?

 

[pbaldy]

See if this helps, using Between and the appropriate functions:

https://support.microsoft.com/en-us/kb/210604

 

[Hecronis]

I couldn't figure out where to plug that in at lol
What ended up doing was creating a query that pulled the previous week using something I found on another site

Year([Payment_Date])*53+DatePart("ww",[Payment_Date])=Year(Date())*53+DatePart("ww",Date())-1

then referenced it in the dsum instead of the table. So far it's working. let me know if you can improve it.

 

[pbaldy]

Well, if it ain't broke don't fix it, but in the DSum() it would have looked like:

DSum("Payment_RentTransaction","TblRent_Report","ID_Number=" & [QryClient_CurrentResidents]![ID_Number] & " And DateField Between FirstFormula And OtherFormula)