IIF question

[Kelli]

Hi, I have a database that tracks transactions and calculates commissions based on a split--income up to $100,000 is paid out at 50%, over $100,000 is paid out at 80%.



My iif references two queries, q_Commissions-Agent_Totals_2012, which uses the sum function to add all the 2012 commissions by agent in an expression, and q-Comm_Agents-2012 uses a multiplier to figure out how much of the total commission each agent is due in an expression (see below).

I'm getting error messages about syntax and commas and can't figure out how to make it work. It appeared to work for previous years (although no one made it to the 80% split) but when I tried to update from the same statement below referencing ***-2011 to 2012 everything broke. Now of course I have not only the year change but one lucky agent who'd like to get paid at the higher rate.

Split NME: IIf([q_Commissions-Agent_Totals_2012]![N Egide]<100000,[q-Comm_Agents-2012]![Comm Amt N Egide]*0.5,IIf([q_Commissions-Agent_Totals_2012]![N Egide]>100000,[q-Comm_Agents-2012]![Comm Amt N Egide]*0.8))

Any help is greatly appreciated!
Kelli

[pbaldy]

Does this work?

Split NME: IIf([q_Commissions-Agent_Totals_2012]![N Egide]<=100000,[q-Comm_Agents-2012]![Comm Amt N Egide]*0.5,[q-Comm_Agents-2012]![Comm Amt N Egide]*0.8)

[June7]

Those are some amazing commission rates!

One issue is the function can't handle case where [N Egide] is exactly 100000 (I know, highly unlikely but not impossible).

The IIf is incomplete. The False option is not provided for the second IIf, however, don't need the second IIf.

Also, what are the fields [N Egide] and [Comm Amt N Egide]. Why is the threshhold test on [N Egide] but the commission is calculated on [Comm Amt N Egide]?

As set up for the agent with transactions greater than 100000, the entire amount will be at 80% commission. If you want the commission paid at 50% for the amount <=100000 and 80% only on the amount that exceeds 100000, need to calculate the difference and apply rates to the two parts.

Split NME: IIf([q_Commissions-Agent_Totals_2012]![N Egide]<=100000, [Comm Amt N Egide]*0.5, ([Comm Amt N Egide]-100000)*0.8 + 100000*0.5)

EDIT: Looks like I was composing reply when Paul posted.

[pbaldy]

EDIT: Looks like I was composing reply when Paul posted.

For 25 minutes.

[June7]

Yeah, sometimes get distracted or what was supposed to be a short quick answer just keeps growing!

[Kelli]

Thanks guys! It's still looking for the parameters for the expressions...to give you a lame excuse I just returned from vacation, only drink decaf and haven't touched my iif statement in over a year! I'm sure that somewhere in my brain is the knowledge that I can't calculate off of an expression in a query and that somewhere else I know what to do about that but it's just not popping up for me. =)

The threshhold is on [N Egide] because that is in the query that totals the year's income. The expression [Comm Amt N Egide] is calculating the percentage due to each agent of the commission for each individual transaction as commissions are paid as earned.

Thanks for any additional help!

Those are some amazing commission rates!

One issue is the function can't handle case where [N Egide] is exactly 100000 (I know, highly unlikely but not impossible).

The IIf is incomplete. The False option is not provided for the second IIf, however, don't need the second IIf.

Also, what are the fields [N Egide] and [Comm Amt N Egide]. Why is the threshhold test on [N Egide] but the commission is calculated on [Comm Amt N Egide]?

As set up for the agent with transactions greater than 100000, the entire amount will be at 80% commission. If you want the commission paid at 50% for the amount <=100000 and 80% only on the amount that exceeds 100000, need to calculate the difference and apply rates to the two parts.

Split NME: IIf([q_Commissions-Agent_Totals_2012]![N Egide]<=100000, [Comm Amt N Egide]*0.5, ([Comm Amt N Egide]-100000)*0.8 + 100000*0.5)

EDIT: Looks like I was composing reply when Paul posted.

[June7]

Show the complete SQL statement that the IIf expression is in. Or provide the project for analysis. Follow instructions at bottom of my post.

[Kelli]

Thanks Super Moderator! Here is the db.

Show the complete SQL statement that the IIf expression is in. Or provide the project for analysis. Follow instructions at bottom of my post.

[June7]

I don't see the IIf expression in any queries.
Which query should the IIf be in?
Your posted expression references query [q-Comm_Agents-2012] that doesn't exist.

Maybe you should just do the IIf calc in a textbox on report?

Why are you building identical queries for each year instead of using input parameter for the year?

[Kelli]

Sorry, the IIf is in the query Commissions-Agents_Splits-2012 and [q_Comm_Agents-2012] was a query that I was testing with and forgot to change that back.

Textbox on a report might work--I just need to be able to determine what to pay to whom per transaction and it really doesn't matter where it calcs.

I think that I spent too many years away from Access so some stuff comes back (like the need to make a nested immediate if statement) but some just doesn't, I sometimes stare at it thinking I know there's a better way but then just deal until it breaks (like now). I do know it needs some serious clean up but I haven't been able to wrap my head around what and how. Like when you say building identical queries for each year instead of using input parameters, I totally know what that means but my brain is saying duh-duh-duh-duh-duh how would I do that? Ugh! To feel like a complete novice again stinks!

I don't see the IIf expression in any queries.
Which query should the IIf be in?
Your posted expression references query [q-Comm_Agents-2012] that doesn't exist.

Maybe you should just do the IIf calc in a textbox on report?

Why are you building identical queries for each year instead of using input parameter for the year?

[June7]

The input parameter could be a prompt within the query but I don't use them because can't validate the input. Recommend the input parameter be a reference to control on form.