Solved

Dsum using dates; multiple criteria

Posted on 2009-07-16
10
1,856 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
 

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 49

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now