Improve company productivity with a Business Account.Sign Up

x
?
Solved

Dsum using dates; multiple criteria

Posted on 2009-07-16
10
Medium Priority
?
2,357 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
  • 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 

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 53

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 2000 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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
With the emergence of Office 365 as a superior email communication platform, many organizations have started switching over to it.  After migrating to Office 365, sometimes users, as well as organizations, will have to import PST files to Office 36…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

606 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