IIF problem in Queries

[zweet18]

FieldName - COMMISSION - store all the commission from 2005 and 2006
FieldName2 - Year - 2005 & 2006

im creating reports so i made query that can compare commission 2005 and 2006
i made these iif statement

Expr1:iif([year]=2006,[commission],"")
Expr2:iif([year]=2005,[commission],"")

Expr1 is not working on my database (blank)
Expr2 is showing


pls help, appreciate a lot

 

[Duane Hookom]

Try:
Commission2006 :Abs([year]=2006) * [commission]
Commission2005 :Abs([year]=2005) * [commission]

Note I use more descriptive column names than Expr1 and Expr2. Another
mistake you made is to create an IIf() that might return a number or a
string. IIf() should return the same data type (or null) no matter what the
true/false expression returns.