IIF Function

[Michael_100]

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?

 

[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.

 

[Michael_100]

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?

 

[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

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?

 

[Michael_100]

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

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

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?

 

[Michael_100]

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

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

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

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?