multiple iif statement NEED HELP PLZ

[scott munkirs]

I hope I can explain this correctly:



I have a report that I want to have a field come up with a caclulation depending on 2 criteria.

The first thing I want it to check for is the field called lastname. this report usually produces 4-8 names upon date selection. I have 2 employees that get paid differently then the other guys. Their calculations will be lower then the other guys.

FIRST --> I need the field in the report to first check the lastname field for these 2 different employees (We will say emp 1 and emp 2). The other employees (We will say emp 3) will have [text44]*0.0815. The 2 different employees will have [text44]*0.0765,

Doesnt sound hard except that the report also needs to determine in the same field, if [monthly bonus] field is >0 for emp 1 and 2. If the [monthly bonus] field is >0 then the following equation occurs: iif([monthly bonus]=0,([text44]-[travel exp])*0.0765,([text44]-[monthly travel])*0.0765)

I am having a tremendous trouble on trying to use multiple iif statements so the field can make the right calculations.

Thanks,

Scott Munkirs

[StepUP]

That's really the wrong approach.

Create a separate table with employee, rate, expenses etc. and then incorporate that table in your query.