• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

Query designer expression

What is wrong with this:

Total: =Sum([Amount]) where [EntryDate] between #1/1/2017# and #10/31/2017#
0
SteveL13
Asked:
SteveL13
  • 4
  • 2
  • 2
  • +2
3 Solutions
 
hnasrCommented:
Where are you using this statement?
Upload the object using it.

First attempt: remove ':'
What error are you receiving?
0
 
SteveL13Author Commented:
Am using in a query designer as an expression.  I can't upload due to confidentiality issues.
0
 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
hnasrCommented:
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.
0
 
SteveL13Author Commented:
Let me see what I can put together
0
 
Gustav BrockCIOCommented:
You can use:

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

Open in new window

/gustav
1
 
PatHartmanCommented:
Where are you trying to use this expression?
Hard-coding dates like this means constant change.  How should this really work?
0
 
SteveL13Author Commented:
I'll get something together tomorrow to show you. I thought this would be easy by if course it isn't.
0
 
PatHartmanCommented:
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.
0
 
SteveL13Author 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".
0
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now