IIF Function - Returning Extra Rows

[accessnewbie352]

I am trying to build an expression using the IIF function in a simple query to calculate only if the IIF function = true.




For example, IIF([Business]="AeroTM",([Total EE]/[Total Dept]),"")


This views me 2 rows of returned data, 1 row will consist of the calculated value and the next row shows the business again, but shows everything else blank (below is what i see in the datasheet view):



Business EE/Dept
AeroTM
AeroTM 42



I know this is because in the IIF function, I have said to return "" if the business does not match. Is there a way I can prevent the blank row from happening by either rewriting the IFF function or by doing something else?


I am new to Access so really only know how to write simple queries.

[June7]

That makes no sense. A calculated field does not create records. The records must exist but the calculation results in null probably because one or both of the referenced fields are null. Arithmetic with Null results in Null. Post the full SQL statement of the query.

[A S MANN]

Try this
EE/Dept: IIF([Business] Like "AeroTM",(Val([Total EE])/Val*([Total Dept])),0)

[accessnewbie352]

It didn't work - it just returned the extra rows with 0 instead of blanks

[amrut]

Can't you use "AeroTM" in the criteria section of the [Business] field and the expression [Total EE]/[Total Dept] as a calculated field in your query ?

[accessnewbie352]

Actually I figured out I can do a crosstab query for my need and then did a form off of it. I now am having an issue with formatting the values of my form but will post this in the form thread. Thank you