Where is the Syntax Error in this MS Access Nested IIF with Parameters Query?

Question

I am trying to create a query in Access that sums the number of Projects Under Consideration and Development as a Month End Inventory. There are three fields I need to get that number dtCreate, dtLegalEnd, and dtFinalClosed, but I also need the code to be dynamic so that it can pull that same sum for a give month, months and years after it has passed. So I tried to do it has a nested IIF parameter query with the following syntax:

SELECT sum(IIF([tblProjectsA].[dtCreate]>[Enter Date End of Month],0,sum(IIF([tblProjectsA].[dtLegalEnd]>[Enter Date End of Month] or is null,0,Sum(IIF([tblProjectsA].[dtFinalClosed]>[Enter Date End of Month] or is null,0,1)))))) FROM tblProjectsA;

Where is my syntax error(s)? Is there a better way to achieve the same result or have the results for each mm/yyyy query?

Thanks, Meg

Answer 1

Try

SELECT sum(IIF([tblProjectsA].[dtCreate]>[Enter Date End of Month],0,sum(IIF([tblProjectsA].[dtLegalEnd]>[Enter Date End of Month] or [tblProjectsA].[dtLegalEnd] is null,0,Sum(IIF([tblProjectsA].[dtFinalClosed]>[Enter Date End of Month] or [tblProjectsA].[dtFinalClosed] is null,0,1)))))) FROM tblProjectsA;

As for having results for each mm/yyyy, you should use GROUP BY clause, like

SELECT Month([your date]) AS Month, Year([your date]) AS Year, ...
FROM ...
GROUP BY Month([your date]), Year([your date])

Answer 2

The error is caused because you are summing a sum. Sum is an sql function that if Sum(column) where column has 5 rows that all = 1, then Sum will return 5. When you try to call sum inside sum Access doesn't officially know what column to pass to the inner sum to get a value to put in the outer sum. So Access throws an error, you can tell access to calculate the inner sum first by wrapping it in a subquery. But I find subqueries are harder to understand and don't have good Access designer support. So instead I use calculated fields which I find more intuitive and declaritive.

ProjectUnderConsideration: IIf(([dtCreate]<[MonthEnd]) And ([dtFinalClosed]<[MonthEnd]) And (([dtLegalEnd]<[MonthEnd]) Or IsNull([dtLegalEnd])),1,0)

I don't understand the construct of ProjectUnderConsideration. Adjust this explanation to your actual case. I assumed a project is under consideration if dtCreate, dtLegalEnd, and dtFinalClosed are all < MonthEnd. and for demonstration I assumed dtLegalEnd could be null.

You can use the Designer in Access to help you write and test your sql:

'sql
PARAMETERS MonthEnd DateTime;
SELECT tblProjectsA.dtCreate, tblProjectsA.dtLegalEnd, tblProjectsA.dtFinalClosed, IIf(([dtCreate]<[MonthEnd]) And ([dtFinalClosed]<[MonthEnd]) And (([dtLegalEnd]<[MonthEnd]) Or IsNull([dtLegalEnd])),1,0) AS ProjectUnderConsideration
FROM tblProjectsA;

Now we have a ProjectUnderConsideration Column to sum. Change the query to a totals query:

PARAMETERS MonthEnd DateTime;
SELECT Sum(IIf(([dtCreate]<[MonthEnd]) And ([dtFinalClosed]<[MonthEnd]) And (([dtLegalEnd]<[MonthEnd]) Or IsNull([dtLegalEnd])),1,0)) AS ProjectUnderConsideration
FROM tblProjectsA;