The IIF() function is used for decision making. The syntax is
IIf(expr, truepart, falsepart)
The "expr" must evaluate to a true or false value. So, for last year, you have "[PaidYear]=Year(Date())-1"
Which means if the field "PaidYear" equals this year -1, the result is TRUE, so return the truepart, otherwise return the falsepart.
You want last years "ExtPrice", so the expression would be:
Code:
LastYear: IIf([PaidYear]=Year(Date())-1,[ExtPrice],0)
This means if the "PaidYear" equals last year, return the "ExtPrice", else return 0 (zero).
For this year, the expression would be:
Code:
ThisYear: IIf([PaidYear]=Year(Date()),[ExtPrice],0)
Lets say there is a table, "tblPayments", with 2 fields: "Paidyear" and "ExtPrice".
There is a query "qryPayments", with the SQL of
Code:
SELECT tblPayments.PaidYear, tblPayments.ExtPrice
FROM tblPayments
ORDER BY tblPayments.PaidYear;
When you execute the query you see the year paid and the amount.
The query is modified to show last year and this year amounts using the expressions above.
It now looks like
Code:
SELECT tblPayments.PaidYear, tblPayments.ExtPrice, IIf([PaidYear]=Year(Date())-1,[ExtPrice],0) AS LastYear, IIf([PaidYear]=Year(Date()),[ExtPrice],0) AS ThisYear
FROM tblPayments
ORDER BY tblPayments.PaidYear;
To get just the year total, the query must be changed to a totals query.
The SQL would be
Code:
SELECT tblPayments.PaidYear, Sum(IIf([PaidYear]=Year(Date()),[ExtPrice],0)) AS ThisYear, Sum(IIf([PaidYear]=Year(Date())-1,[ExtPrice],0)) AS LastYear
FROM tblPayments
GROUP BY tblPayments.PaidYear;
To have only one record, the "PaidYear" column would have to be removed:
Code:
SELECT Sum(IIf([PaidYear]=Year(Date()),[ExtPrice],0)) AS ThisYear, Sum(IIf([PaidYear]=Year(Date())-1,[ExtPrice],0)) AS LastYear
FROM tblPayments;
Hope this helps.......