DSum Multiple Conditions

USING MICROSOFT ACCESSXP

I have a control on a report looking up the total of hours for the month from the time table.  I cannot add this table/query of the table into the main underlying query of the report because there wouldn't be a valid join.  

I need the sum of the Hours between to two dates.  The dates are parameters of the report.

My DSum works if I use one condition but I'm have #Error messages in the control for multiple conditions or if I use the between statement.  

FIRST DATE
[Enter Ending Month Date for WIP Period]
e.g., 01/31/05

SECOND DATE
[Enter Ending Date for Prior Month]
e.g., 12/31/04

I want the range that is >[Enter Ending Date for Prior Month] and <=[Enter Ending Month Date for WIP Period]

The only formula I have working at present is:
=DSum("[HoursWorked]","Time Billed","[WorkDate]<=[Enter Ending Month Date for WIP Period]")


The other formulas I'm working on that do not work are:
=DSum("[HoursWorked]","Time Billed","[WorkDate]>[Enter Ending Date for Prior Month] And [WorkDate]<=[Enter Ending Month Date for WIP Period]")
=DSum("[HoursWorked]","Time Billed","[WorkDate] Between ([Enter Ending Date for Prior Month]+1) And [Enter Ending Month Date for WIP Period]")

I have even tried the work around based on three controls.  One that would calculate the grand total through [Enter Ending Month Date for WIP Period] and then one calculating through [Enter Ending Date for Prior Month] separately.  The difference between these two calculations would yield the result I need.  HOWEVER...the DSum expression for the prior period isn't working either.  

=DSum("[HoursWorked]","Time Billed","[WorkDate]<=[Enter Ending Date for Prior Month]")

Going to continue to play with the expressions.  I would pressume this ought to be able to work in a single DSum, but if it takes three controls ... fine by me.  I'm interested in the results however I get there.

Thanks!
Joseph



JKubonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JKubonAuthor Commented:
These items are in the Report Footer section.

Once I put a control in the header for the SECOND DATE prompt, the individual formulas worked.  The FIRST DATE prompt already had a reference in the header of the report.

Right now I'm getting accurate results using the 3 separate field method.  Will be testing the multiple conditions now.

0
JKubonAuthor Commented:
With the multiple conditions...

This produces expected results:
DSum("[HoursWorked]","Time Billed","[WorkDate]>[Enter Ending Date for Prior Month] And [WorkDate]<=[Enter Ending Month Date for WIP Period]")

This produces results but the amount is wrong:
=DSum("[HoursWorked]","Time Billed","[WorkDate] Between ([Enter Ending Date for Prior Month]+1) And [Enter Ending Month Date for WIP Period]")


0
Gustav BrockCIOCommented:
You might try this as your input are strings:

=DSum("[HoursWorked]","Time Billed","[WorkDate]>DateValue([Enter Ending Date for Prior Month]) And [WorkDate]<=DateValue([Enter Ending Month Date for WIP Period])")

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Did this work for you?

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

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.