IIF Function in Query Access

Question

I always get an error message"You tried to execute a query that does not include the specified expression as part of an aggregate function."

Expr1:

IIf([Expiry Date]=Date(),"Due today","Not Yet Due")

also I want to count all the Due Date. What is the correct expression for this.

Hope someone can help. Thank you

The error message is not related to the IIF code you posted, based on that short (entirely out of any context) code snippet. Please provide a [mcve] that demonstrates the issue. — Ken White, Dec 04 '19 at 18:03
Not with the information you've provided (which is none). Please provide a [mcve] that demonstrates the issue. — Ken White, Dec 04 '19 at 18:05
You'll at least need to post the rest of the SQL constituting your query for us to be able to help. — Lee Mac, Dec 04 '19 at 19:00

score 0 · Answer 1 · answered Dec 15 '19 at 05:34

You are obviously trying to create an aggregate query. Each field or expression must be assigned an aggregate function. In this case it appears need to GROUP BY results of the calculated field which means including in SELECT and GROUP BY clauses of aggregate query. Then an aggregation can give a count by the two values "Due today" and "Not Yet Due".

SELECT IIf([Expiry Date]=Date(), "Due today", "Not Yet Due") AS Status, Count(*) AS CountStatus 
FROM tablename 
GROUP BY IIf([Expiry Date]=Date(), "Due today", "Not Yet Due");

Then I expect want to consider only records where [Expiry Date] is in future.
WHERE [Expiry Date] >= Date()

IIF Function in Query Access

1 Answers1