Nested IIF Statement syntax(?) problem

  • Thread starter Thread starter prodeji
  • Start date Start date
P

prodeji

Hi everyone

I'm trying to write a nested IIF statement to calculate commission for
an ROI report.

The particulars are:

If revenue <=0, commission = $0.00.

If revenue between 0 and $166999.00, commission = [revenue * 0.15]

If revenue >= $167000.00, commission = $25000.00



I first tried using IIF(Test, IIF(Test, when true, when false),when
false) to nest the statements:

IIF(([TableWL]+[SlotsWL])>0, IIF([TableWl+SlotsWL]>167000,25000,
[TableWl+SlotsWL]*0.15),0)

Then I tried scrapping the 'else' for the outer IIF and just writing
all three conditions 'straight':

IIF([TableWL]+[SlotsWL]<=0,0), IIF([TableWL]+[SlotsWL] between 0 and
166999, [TableWl+SlotsWL]*0.15), IIF([TableWl+SlotsWL]>167000,25000)


On the first I get an error message that says "Extra ) in query
expression".

On the second I get an "Syntax error (comma) in query expression"
message.
To the best of my knowledge there is nothing wrong with the query or
syntax.

Can anyone show me the error of my ways?

Thanks,

prodeji
 
prodeji said:
Hi everyone

I'm trying to write a nested IIF statement to calculate commission for
an ROI report.

The particulars are:

If revenue <=0, commission = $0.00.

If revenue between 0 and $166999.00, commission = [revenue * 0.15]

If revenue >= $167000.00, commission = $25000.00



I first tried using IIF(Test, IIF(Test, when true, when false),when
false) to nest the statements:

IIF(([TableWL]+[SlotsWL])>0, IIF([TableWl+SlotsWL]>167000,25000,
[TableWl+SlotsWL]*0.15),0)

Then I tried scrapping the 'else' for the outer IIF and just writing
all three conditions 'straight':

IIF([TableWL]+[SlotsWL]<=0,0), IIF([TableWL]+[SlotsWL] between 0 and
166999, [TableWl+SlotsWL]*0.15), IIF([TableWl+SlotsWL]>167000,25000)


On the first I get an error message that says "Extra ) in query
expression".

On the second I get an "Syntax error (comma) in query expression"
message.
To the best of my knowledge there is nothing wrong with the query or
syntax.

Can anyone show me the error of my ways?

Thanks,

prodeji
Click to expand...

Hi prodeji,
i think the error is in first expression is in [TableWl+SlotsWL]
the correct one should be:

IIF(([TableWL]+[SlotsWL])>0,
IIF(([TableWl]+[SlotsWL])>167000,25000,
([TableWl]+[SlotsWL])*0.15),0)

in the second you have misplaced the ) and you have done the same error of
first exp [TableWl+SlotsWL]

IIF(([TableWL]+[SlotsWL])<=0,0,
IIF(([TableWL]+[SlotsWL])<=166999, ([TableWl]+[SlotsWL])*0.15,25000)
)

Bye
 
I'm trying to write a nested IIF statement to calculate commission for
an ROI report.

The particulars are:

If revenue <=0, commission = $0.00.

If revenue between 0 and $166999.00, commission = [revenue * 0.15]

If revenue >= $167000.00, commission = $25000.00
Click to expand...

Try using the Switch() function instead of IIF. It takes any
(reasonable) number of pairs of arguments, and goes through them left
to right; it returns the second member of the first pair for which the
first argument is true. E.g.

=Switch([Revenue] <= 0, 0, [Revenue] < 167000, [Revenue] * 0.15, True,
25000)

I think the problem with your IIF's was some missing square brackets,
though: your examples had

[TableWl+SlotsWL]

which should have been

[TableWl]+[SlotsWL]


John W. Vinson[MVP]
 
Hi Cinzia,

Thanks for replying.

Your analysis of the problem makes sense, and I tried both of them, but
I got the same "Extra ) in query" error message.

I'm using Access 2000, do you think this may be (contributing to) the
problem?

Also, if there is any other way of doing this I am open to trying, it
doesn't have to be an IIF statement.

I tried a switch statement previously but didn't have any luck with
that either.

prodeji

prodeji said:
Hi everyone

I'm trying to write a nested IIF statement to calculate commission for
an ROI report.

The particulars are:

If revenue <=0, commission = $0.00.

If revenue between 0 and $166999.00, commission = [revenue * 0.15]

If revenue >= $167000.00, commission = $25000.00



I first tried using IIF(Test, IIF(Test, when true, when false),when
false) to nest the statements:

IIF(([TableWL]+[SlotsWL])>0, IIF([TableWl+SlotsWL]>167000,25000,
[TableWl+SlotsWL]*0.15),0)

Then I tried scrapping the 'else' for the outer IIF and just writing
all three conditions 'straight':

IIF([TableWL]+[SlotsWL]<=0,0), IIF([TableWL]+[SlotsWL] between 0 and
166999, [TableWl+SlotsWL]*0.15), IIF([TableWl+SlotsWL]>167000,25000)


On the first I get an error message that says "Extra ) in query
expression".

On the second I get an "Syntax error (comma) in query expression"
message.
To the best of my knowledge there is nothing wrong with the query or
syntax.

Can anyone show me the error of my ways?

Thanks,

prodeji
Click to expand...

Hi prodeji,
i think the error is in first expression is in [TableWl+SlotsWL]
the correct one should be:

IIF(([TableWL]+[SlotsWL])>0,
IIF(([TableWl]+[SlotsWL])>167000,25000,
([TableWl]+[SlotsWL])*0.15),0)

in the second you have misplaced the ) and you have done the same error of
first exp [TableWl+SlotsWL]

IIF(([TableWL]+[SlotsWL])<=0,0,
IIF(([TableWL]+[SlotsWL])<=166999, ([TableWl]+[SlotsWL])*0.15,25000)
)

Bye
Click to expand...
 
John, that seems to be working, thanks.

The only "iif" <giggle, pardon my database humor> seems to be this; I
have a returned commission figure of $29000 plus. If I wrote my formula
correctly, I shouldn't get any figures topping $25000.00.

Anyhoo, that's something I'll have to figure out for myself.

Thanks, guy.

John said:
I'm trying to write a nested IIF statement to calculate commission for
an ROI report.

The particulars are:

If revenue <=0, commission = $0.00.

If revenue between 0 and $166999.00, commission = [revenue * 0.15]

If revenue >= $167000.00, commission = $25000.00
Click to expand...

Try using the Switch() function instead of IIF. It takes any
(reasonable) number of pairs of arguments, and goes through them left
to right; it returns the second member of the first pair for which the
first argument is true. E.g.

=Switch([Revenue] <= 0, 0, [Revenue] < 167000, [Revenue] * 0.15, True,
25000)

I think the problem with your IIF's was some missing square brackets,
though: your examples had

[TableWl+SlotsWL]

which should have been

[TableWl]+[SlotsWL]


John W. Vinson[MVP]
Click to expand...
 
Back
Top