Query designer expression

SteveL13
SteveL13 used Ask the Experts™
on
What is wrong with this:

Total: =Sum([Amount]) where [EntryDate] between #1/1/2017# and #10/31/2017#
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hamed NasrRetired IT Professional

Commented:
Where are you using this statement?
Upload the object using it.

First attempt: remove ':'
What error are you receiving?

Author

Commented:
Am using in a query designer as an expression.  I can't upload due to confidentiality issues.
The query expression can result in a field, but it can not directly have its own where clause, UNLESS you make it into a  subquery (which can be correlated if need be), or you could use a Dsum expression, but beware of performance implications of doing that, if implemented wrongly.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hamed NasrRetired IT Professional

Commented:
I can't upload due to confidentiality issues.
If I want to check, I need to recreate the issue, and not your database.
So I am asking you to recreate the issue with dummy data.

Author

Commented:
Let me see what I can put together
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can use:

Total: =Sum(IIf([EntryDate]>=#1/1/2017# And [EntryDate]<=#10/31/2017#,[Amount],0))

Open in new window

/gustav
Distinguished Expert 2017
Commented:
Where are you trying to use this expression?
Hard-coding dates like this means constant change.  How should this really work?

Author

Commented:
I'll get something together tomorrow to show you. I thought this would be easy by if course it isn't.
Distinguished Expert 2017

Commented:
Gus gave you a suggestion for how to incorporate the date range in the sum but context matters and the expression would be different depending on where you needed to run it.

Author

Commented:
I was able to use Gus' code with modification:

TotalCurrentPeriod: Sum(IIf([PostDate]>=[Forms]![frmSelectDateRange]![txtStartDate] And [PostDate]<=[Forms]![frmSelectDateRange]![txtEndDate],[Amount],0))

But I'm going to award points to all who contributed, because everyone provided insight that really helps me, and start a new topic title "Query Designer Help Please".

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial