Solved

Dsum using dates; multiple criteria

Posted on 2009-07-16
10
2,065 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:schaIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 24872781
>>  "' & [DonationID] & '"
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)
 

0
 

Author Comment

by:schaIT
ID: 24872852
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?
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 24877873
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?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:schaIT
ID: 24882683
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?
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 24883177
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
0
 
LVL 27

Accepted Solution

by:
MikeToole earned 500 total points
ID: 24885573
As /gustav says, DSum iwill do the job.
On my second post I missed that you are using the expression as the source of a control, so there souldn't be a line of code to break on, sorry for the wrong pointer.
The control shows #Error, that says that there was a prblem when it tried to evaluate the expression.
What is needed is to check the expression itself, in isolation from the report
DSUM() takes 3 parameters - the field to Sum, the table/query source and the selection Criteria.
You're using concatenation to build the Criteria expression:
"[DonationID] = "' & [DonationID] & "' AND [TransactionDate] Between #1/1/2009# And #6/30/2009#"
This will result in "[DonationID]='123' AND [TransactionDate] Between #1/1/2009# And #6/30/2009#" if there is a value 123 in [DonationID].
This expression will fail with a datatype mismatch if the column [DonationID] is numeric - the single quotes round the value denote a string variable
Try it without the single quotes.

You can test this in the Immediate window by hard coding a known DonationID, let's assume 123, and hitting enter:
?DSum("[PaymentAmt]","qryDonationsbyQtrbyYr","[DonationID] =123 AND [TransactionDate] Between #1/1/2009# And #6/30/2009#")

0
 

Author Comment

by:schaIT
ID: 24896160
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]","tlkpDonationDetails","[DonationID] = " & [DonationID] & " AND [TransactionDate] Between [forms]![Report Date Range]![Beginning Date] And [forms]![Report Date Range]![Ending Date]")

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?

0
 
LVL 27

Expert Comment

by:MikeToole
ID: 24902225
...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]","tlkpDonationDetails","[DonationID] = " & [DonationID] & " AND [TransactionDate] Between (Date() - format(Date(),"y") + 1) And Date() "

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
0
 

Author Comment

by:schaIT
ID: 24952729
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.
0
 

Author Closing Comment

by:schaIT
ID: 31604373
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.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question