Dsum using dates; multiple criteria
Posted on 2009-07-16
I have a report that I have been working on for the past couple of days and it is driving me bonkers. I am not an expert in Access, VB, or just plain syntax for that matter. I am mostly self taught and use whatever Google may find when in a bind.
Here is my issue:
I have a report that consists of 4 subreports. The main report is called rptEthics. The subreport is called rptEthicsPg3. I did not encounter any issues connecting rptEthicsPg3 to the main report. rptEthicsPg3 is the subreport that is giving me a headache.
rptEthicspg3 is based on a query called 'qryDonationsbyQtrbyYr'.The following controls are on the rptEthicsPg3: DonationID, TransactionDate, FullName, FullAddress, PaymentAmt, and SumPymtAmt. All of the controls are bound except SumPymtAmt. When the main report is run, a form(Report Date Range) prompts for a date range. The main report along with the subreports are filtered based on the date range chosen.
I would like the SumPymtAmt control to sum all payments for each DonationID (person) year-to-date based on the date range chosen. For example: If the date range chosen is 1/1/2009 to 6/30/2009, I would like the SumPymtAmt to show all payments made by that person from 1/1/2009 up to and including 6/30/2009.
I made an attempt to use the DSum function as the control source of SumPymtAmt to see if that would do the trick. Below is what I came up with (I used hard dates just to see if it would work, not sure what the syntax would be to pull in the dates from the form 'Report Date Range'):
=Nz(DSum("[PaymentAmt]","qryDonationsbyQtrbyYr","[DonationID] = "' & [DonationID] & '" AND [TransactionDate] Between #1/1/2009# And #6/30/2009#"))
I keep getting an error message that says, "The expression you entered contains invalid syntax. You may have entered an operand without an operator."
I am not sure how to fix this issue. Any suggestions on what I have so far or a better way to do this would be greatly appreciated.