IIF Function

G

Guest

I am working on a report where I need the report to return a value based on a
percentage number in another field. The field containing the percentage is
[Avg Of TCF Evaluation]. If that field contains a percent from 95 to 100, I
want an unbound control to return a point value of 40. If that field contains
a percent from 92 to 94.9, I want the unbound control to return a point value
of 35 and so on. I am trying to use the IIf function without luck.

My formula looks something like:

IIF(([Avg Of TCF Evaluation] >= 92% And ([Avg Of TCF Evaluation] <= 95%, 35)
, IIF([Avg Of TCF Evaluation] >=95%, 40, 31))

I keep getting a syntax error. I have tried adjusting the formula many
different ways, yet the error persists. Can someone help me with the right
formula?
 
W

Wayne Morgan

A couple of problems.

1) The parentheses don't line up properly.

2) Try 0.92 instead of 92%.


Judging by what you have, it appears that you want anything less than 92% to
return 31, is this correct? If so, try:

IIf([Avg Of TCF Evaluation] >= 0.95, 40, IIf([Avg Of TCF Evaluation] >=
0.92, 35, 31))

If you line them up, you don't need to test for the second half of the
"between" because if the first part is True, it won't matter if the False
part (the next IIf) is also True.
 
G

Guest

Wayne,

Thank you for your response, however after trying your suggestion exactly as
written, I am still getting a syntax error. The error reads: Syntax error in
query expression '[IIf([Avg Of TCF Evaluation] >=0].[95,40, IIf([Avg Of TCF
Evaluation] >= 0].[92, 35, 31))]'
--
Michael


Wayne Morgan said:
A couple of problems.

1) The parentheses don't line up properly.

2) Try 0.92 instead of 92%.


Judging by what you have, it appears that you want anything less than 92% to
return 31, is this correct? If so, try:

IIf([Avg Of TCF Evaluation] >= 0.95, 40, IIf([Avg Of TCF Evaluation] >=
0.92, 35, 31))

If you line them up, you don't need to test for the second half of the
"between" because if the first part is True, it won't matter if the False
part (the next IIf) is also True.

--
Wayne Morgan
MS Access MVP


Michael_100 said:
I am working on a report where I need the report to return a value based on
a
percentage number in another field. The field containing the percentage is
[Avg Of TCF Evaluation]. If that field contains a percent from 95 to 100,
I
want an unbound control to return a point value of 40. If that field
contains
a percent from 92 to 94.9, I want the unbound control to return a point
value
of 35 and so on. I am trying to use the IIf function without luck.

My formula looks something like:

IIF(([Avg Of TCF Evaluation] >= 92% And ([Avg Of TCF Evaluation] <= 95%,
35)
, IIF([Avg Of TCF Evaluation] >=95%, 40, 31))

I keep getting a syntax error. I have tried adjusting the formula many
different ways, yet the error persists. Can someone help me with the right
formula?
Click to expand...
Click to expand...
 
W

Wayne Morgan

Did you put an equals sign in front of it? A calculated control starts with
an equal sign:

=IIf([Avg Of TCF Evaluation] >= 0.95, 40, IIf([Avg Of TCF Evaluation] >=
0.92, 35, 31))

Also, the newsreader will wrap the line, it should all be on one line.


--
Wayne Morgan
MS Access MVP


Michael_100 said:
Wayne,

Thank you for your response, however after trying your suggestion exactly
as
written, I am still getting a syntax error. The error reads: Syntax error
in
query expression '[IIf([Avg Of TCF Evaluation] >=0].[95,40, IIf([Avg Of
TCF
Evaluation] >= 0].[92, 35, 31))]'
--
Michael


Wayne Morgan said:
A couple of problems.

1) The parentheses don't line up properly.

2) Try 0.92 instead of 92%.


Judging by what you have, it appears that you want anything less than 92%
to
return 31, is this correct? If so, try:

IIf([Avg Of TCF Evaluation] >= 0.95, 40, IIf([Avg Of TCF Evaluation] >=
0.92, 35, 31))

If you line them up, you don't need to test for the second half of the
"between" because if the first part is True, it won't matter if the False
part (the next IIf) is also True.

--
Wayne Morgan
MS Access MVP


Michael_100 said:
I am working on a report where I need the report to return a value based
on
a
percentage number in another field. The field containing the percentage
is
[Avg Of TCF Evaluation]. If that field contains a percent from 95 to
100,
I
want an unbound control to return a point value of 40. If that field
contains
a percent from 92 to 94.9, I want the unbound control to return a point
value
of 35 and so on. I am trying to use the IIf function without luck.

My formula looks something like:

IIF(([Avg Of TCF Evaluation] >= 92% And ([Avg Of TCF Evaluation] <=
95%,
35)
, IIF([Avg Of TCF Evaluation] >=95%, 40, 31))

I keep getting a syntax error. I have tried adjusting the formula many
different ways, yet the error persists. Can someone help me with the
right
formula?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks, Wayne. That did it. The values do not stop at 2, there are actually
7 possible returns based on the value in [Avg Of TCF Evaluation]. Can I carry
this out based on the following without an error?:

Avg Of TCF Return
..95 - 1.0 40
..92 - .949 35
..87 - .919 31
..82 - .869 24
..75 - .819 20
..70 - .749 10
<70 0

Thank you for your help!

--
Michael


Wayne Morgan said:
Did you put an equals sign in front of it? A calculated control starts with
an equal sign:

=IIf([Avg Of TCF Evaluation] >= 0.95, 40, IIf([Avg Of TCF Evaluation] >=
0.92, 35, 31))

Also, the newsreader will wrap the line, it should all be on one line.


--
Wayne Morgan
MS Access MVP


Michael_100 said:
Wayne,

Thank you for your response, however after trying your suggestion exactly
as
written, I am still getting a syntax error. The error reads: Syntax error
in
query expression '[IIf([Avg Of TCF Evaluation] >=0].[95,40, IIf([Avg Of
TCF
Evaluation] >= 0].[92, 35, 31))]'
--
Michael


Wayne Morgan said:
A couple of problems.

1) The parentheses don't line up properly.

2) Try 0.92 instead of 92%.


Judging by what you have, it appears that you want anything less than 92%
to
return 31, is this correct? If so, try:

IIf([Avg Of TCF Evaluation] >= 0.95, 40, IIf([Avg Of TCF Evaluation] >=
0.92, 35, 31))

If you line them up, you don't need to test for the second half of the
"between" because if the first part is True, it won't matter if the False
part (the next IIf) is also True.

--
Wayne Morgan
MS Access MVP


I am working on a report where I need the report to return a value based
on
a
percentage number in another field. The field containing the percentage
is
[Avg Of TCF Evaluation]. If that field contains a percent from 95 to
100,
I
want an unbound control to return a point value of 40. If that field
contains
a percent from 92 to 94.9, I want the unbound control to return a point
value
of 35 and so on. I am trying to use the IIf function without luck.

My formula looks something like:

IIF(([Avg Of TCF Evaluation] >= 92% And ([Avg Of TCF Evaluation] <=
95%,
35)
, IIF([Avg Of TCF Evaluation] >=95%, 40, 31))

I keep getting a syntax error. I have tried adjusting the formula many
different ways, yet the error persists. Can someone help me with the
right
formula?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Wayne, Thank you so much! I figured it out!
--
Michael


Michael_100 said:
Thanks, Wayne. That did it. The values do not stop at 2, there are actually
7 possible returns based on the value in [Avg Of TCF Evaluation]. Can I carry
this out based on the following without an error?:

Avg Of TCF Return
.95 - 1.0 40
.92 - .949 35
.87 - .919 31
.82 - .869 24
.75 - .819 20
.70 - .749 10
<70 0

Thank you for your help!

--
Michael


Wayne Morgan said:
Did you put an equals sign in front of it? A calculated control starts with
an equal sign:

=IIf([Avg Of TCF Evaluation] >= 0.95, 40, IIf([Avg Of TCF Evaluation] >=
0.92, 35, 31))

Also, the newsreader will wrap the line, it should all be on one line.


--
Wayne Morgan
MS Access MVP


Michael_100 said:
Wayne,

Thank you for your response, however after trying your suggestion exactly
as
written, I am still getting a syntax error. The error reads: Syntax error
in
query expression '[IIf([Avg Of TCF Evaluation] >=0].[95,40, IIf([Avg Of
TCF
Evaluation] >= 0].[92, 35, 31))]'
--
Michael


:

A couple of problems.

1) The parentheses don't line up properly.

2) Try 0.92 instead of 92%.


Judging by what you have, it appears that you want anything less than 92%
to
return 31, is this correct? If so, try:

IIf([Avg Of TCF Evaluation] >= 0.95, 40, IIf([Avg Of TCF Evaluation] >=
0.92, 35, 31))

If you line them up, you don't need to test for the second half of the
"between" because if the first part is True, it won't matter if the False
part (the next IIf) is also True.

--
Wayne Morgan
MS Access MVP


I am working on a report where I need the report to return a value based
on
a
percentage number in another field. The field containing the percentage
is
[Avg Of TCF Evaluation]. If that field contains a percent from 95 to
100,
I
want an unbound control to return a point value of 40. If that field
contains
a percent from 92 to 94.9, I want the unbound control to return a point
value
of 35 and so on. I am trying to use the IIf function without luck.

My formula looks something like:

IIF(([Avg Of TCF Evaluation] >= 92% And ([Avg Of TCF Evaluation] <=
95%,
35)
, IIF([Avg Of TCF Evaluation] >=95%, 40, 31))

I keep getting a syntax error. I have tried adjusting the formula many
different ways, yet the error persists. Can someone help me with the
right
formula?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIf function 7
Complicated IIf Statement Problem 24
iif statement 2
Is that your Final Answer????? 13
IIF function alternative 3
Access IIF Statement - If field is greater than other field by =/-percentage 0
iif question 13
Multiple iif challenge 2

Top