Dsum using dates; multiple criteria
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'.Th
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]","q
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.
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'.Th
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]","q
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.
ASKER
Thanks for the help on the syntax. I will have to research how to use the immediate window in vba. That cured the error message, but when I run the report the control shows #Error.
Any suggestions?
Any suggestions?
Looks like the statement doesn't result in a valid value.
Put a breakpoint on the statement in your code then type this in the Immediate window (The ? is a Print command)
?"[DonationID] = "' & [DonationID] & "' AND [TransactionDate] Between #1/1/2009# And #6/30/2009#"
That will at least show you the criteria you're supplying.
What is the source of the value for DonationID that you're concatenating into the string?
Put a breakpoint on the statement in your code then type this in the Immediate window (The ? is a Print command)
?"[DonationID] = "' & [DonationID] & "' AND [TransactionDate] Between #1/1/2009# And #6/30/2009#"
That will at least show you the criteria you're supplying.
What is the source of the value for DonationID that you're concatenating into the string?
ASKER
I think my attempt at this is ill-fated. I do not want to concatenate a string. So my understanding of the DSum function must be off.
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.
Is there another function I can use to achieve this or is it just impossible?
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.
Is there another function I can use to achieve this or is it just impossible?
Your usage of DSum is correct.
It sounds like the textbox you are trying to assign the sum is named PaymentAmt.
Try to rename it.
/gustav
It sounds like the textbox you are trying to assign the sum is named PaymentAmt.
Try to rename it.
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You rock! The DSum() worked after removing the single quotes. Hopefully you can assist with my last issue. Now that the sum works, I want to pull in the date values from the form 'Report Date Range' instead of using hard dates in the formula. I have completed this action by updating the formula as shown below:
=DSum("[PaymentAmt]","tlkp
What I would like from this point is for SumPymtAmt to show a year-to-date sum based on the dates chosen on the form 'Report Date Range'. I know there is a expression that can be used in a query based on the current date, but I am not sure how it would work in the control source pulling a date from the 'Report Date Range' form.
Any ideas?
=DSum("[PaymentAmt]","tlkp
What I would like from this point is for SumPymtAmt to show a year-to-date sum based on the dates chosen on the form 'Report Date Range'. I know there is a expression that can be used in a query based on the current date, but I am not sure how it would work in the control source pulling a date from the 'Report Date Range' form.
Any ideas?
...I want to pull in the date values from the form 'Report Date Range' ...
...What I would like from this point is for SumPymtAmt to show a year-to-date sum based on the dates chosen on the form 'Report Date Range'...
Are there two separate requirements?
A Year-to-date sum wouldn't require any dates from the form since it would start on Jan 1st and end on the run date:
=DSum("[PaymentAmt]","tlkp
This gets the beginning of the year by subtracting the day number (Format(Date(),"y") from the current date and adding 1
Hope this helps
...What I would like from this point is for SumPymtAmt to show a year-to-date sum based on the dates chosen on the form 'Report Date Range'...
Are there two separate requirements?
A Year-to-date sum wouldn't require any dates from the form since it would start on Jan 1st and end on the run date:
=DSum("[PaymentAmt]","tlkp
This gets the beginning of the year by subtracting the day number (Format(Date(),"y") from the current date and adding 1
Hope this helps
ASKER
This helps. What I am referring to in year-to-date is as follows:
If the date range chosen is 1/1/2008 to 6/30/2008, I would like the SumPymtAmt to show all payments made by that person from 1/1/2008 up to and including 6/30/2008. If the date range 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.
Each time the report runs the sum should include all payments from the beginning of the year for the beginning date chosen through the ending date chosen.
If the date range chosen is 1/1/2008 to 6/30/2008, I would like the SumPymtAmt to show all payments made by that person from 1/1/2008 up to and including 6/30/2008. If the date range 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.
Each time the report runs the sum should include all payments from the beginning of the year for the beginning date chosen through the ending date chosen.
ASKER
Thank you so much for your help. I figured out the last piece to the puzzle. You are awesome! Thanks so much for your time and knowledge.
You have the single and double quote marks the wrong way round, should be:
'" & [DonationID] & "'
(Spot the difference! :-) - I used the Access vba immediate window to figure out what the resultant string would be, it's easy to see it then)