Sum (Aggregate) subquery using fields in query as criteria

Hello - I'm trying to put together an Access 2016 query that has a field that calculates an aggregate Sum, dependent on a subset of date in that same query. After multiple subqueries, separate queries, and even using a report date field in a row to key off of, the date criteria does not use the date in the query and rather uses the entire year, which is the hard coded criteria in the query.

I'm open to any approach to basically use a date range defined in fields in the query, in order to be used as a subquery's criteria. More specifically, and using the output and code attached, we need to use the dates in INV_APP_DATE and TWO_COM_RECIPIENT_ID as parameters for the subquery that totals up INV_APP_AMOUNT * TWO_COM_CREDIT, in the field NetCredited. There is a hard coded parameter of dates using only 2017. So if the date in INV_APP_DATE is 4/1/2017, then the total of INV_APP_AMOUNT * TWO_COM_CREDIT should include all records from 1/1/2017 to 4/1/2017. But instead, the total reflects ALL records in 2017, which is the 6765739 seen in the screen shot. It's as if the specific parameter on INV_APP_DATE isn't used, no matter how it's coded.

Attached is a screenshot and the SQL as well. Any ideas would be great.
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.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
This would be alot easier to help with if you could provide a sample of the database, just with the table itself, and the query you are trying to modify.
the total reflects ALL records in 2017, which is the 6765739

Are you sure that's the total for 2017 and not for 2017 and prior years? The date filter in the subquery just says where the date is less than or equal to INV_LINE_APPLICATIONS.INV_APP_DATE:


Open in new window

 If it must also be in 2017 only, you must to specify that in the subquery too.

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
Kamlesh JainManagerCommented:
hi. I am not an expert but i guess wrong results may appear if correct date format is not used.

e.g. Format(Me.PoDateFrom.Value, "YYYY\/mm\/dd").
We are using DD/MM/YYYY format
Kamlesh Jain
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

maknitCEOAuthor Commented:
Thank you everyone. Here's a quick response to all posts, and I apologize for the delay. Been caught up in some priority fire drills at my business.

To Anders - Yes, maybe I can do that after this next round. I haven't so far, as there are multiple tables and they are big.
To agx - The reason why it doesn't bring in years prior to 2017 is that there's another query parameter hard-coding the year as 2017. But this does make me wonder if that other parameter might be overriding the sub-query.
To Kamlesh - yes, I've used date formats (#17/04/2004#) with no success, but haven't used the exact same syntax as you write. You're basically changing the format or sequencing of years, months, days. Don't know why this would change anything but will give it a try.

If anyone has any other ideas, please let me know. Otherwise, I will try to get a sample DB posted. Thanks.
maknitCEOAuthor Commented:
Hello - I'm closing this out. In the process of setting up a test db, I found out that it was the combination of a hard coded parameter and the date range parameters (hard coded over-rode the date range). So it will be a pretty quick solution at this point. Thanks for all the comments.
maknitCEOAuthor Commented:
As the last comment indicates - the problem was the combination of a hard coded query parameter taking precedence over a date range parameter.
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

From novice to tech pro — start learning today.