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.