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]>.95,40,IIf[Avg Of TCF Evaluation]>.92<.949,35))

I am sure I am just confusing Access. Can someone help me with the right
formula?
 
G

Guest

ALso, I should say that I have the values set up in a table, if that would be
easier to draw from. The table has LowRef, HighRef and Correction. Would it
be easier to get the control to draw from the table based on the value in
[Avg Of TCF Evaluation]? If so, how can I accomplish this?
 
G

Guest

Lynn, thank you for your reply. I tried your formula but I keep ketting a
syntax error. I have tried to adjust the formula, yet the error persists.
What am I doing wrong?
--
Michael


Lynn Trapp said:
Try IIF(([Avg Of TCF Evaluation] >= .92 And [Avg Of TCF Evaluation] < .95),
35,IIF( [Avg Of TCF Evaluation] >= .95, 40,SomeOtherNumber))

'SomeOtherNumber' would be for anything that is below .92

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



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]>.95,40,IIf[Avg Of TCF
Evaluation]>.92<.949,35))

I am sure I am just confusing Access. Can someone help me with the right
formula?
Click to expand...
Click to expand...
 
L

Lynn Trapp

Michael,
Please post exactly what you used that is getting the syntax error.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Michael_100 said:
Lynn, thank you for your reply. I tried your formula but I keep ketting a
syntax error. I have tried to adjust the formula, yet the error persists.
What am I doing wrong?
--
Michael


Lynn Trapp said:
Try IIF(([Avg Of TCF Evaluation] >= .92 And [Avg Of TCF Evaluation] <
.95),
35,IIF( [Avg Of TCF Evaluation] >= .95, 40,SomeOtherNumber))

'SomeOtherNumber' would be for anything that is below .92

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



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]>.95,40,IIf[Avg Of TCF
Evaluation]>.92<.949,35))

I am sure I am just confusing Access. Can someone help me with the
right
formula?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Lynn, I tried all of:

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

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

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

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

The first is your suggestion. The rest are variants I have tried.
--
Michael


Lynn Trapp said:
Michael,
Please post exactly what you used that is getting the syntax error.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Michael_100 said:
Lynn, thank you for your reply. I tried your formula but I keep ketting a
syntax error. I have tried to adjust the formula, yet the error persists.
What am I doing wrong?
--
Michael


Lynn Trapp said:
Try IIF(([Avg Of TCF Evaluation] >= .92 And [Avg Of TCF Evaluation] <
.95),
35,IIF( [Avg Of TCF Evaluation] >= .95, 40,SomeOtherNumber))

'SomeOtherNumber' would be for anything that is below .92

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



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]>.95,40,IIf[Avg Of TCF
Evaluation]>.92<.949,35))

I am sure I am just confusing Access. Can someone help me with the
right
formula?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I have it Lynn. I was missing the = sign.
--
Michael


Michael_100 said:
Lynn, I tried all of:

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

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

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

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

The first is your suggestion. The rest are variants I have tried.
--
Michael


Lynn Trapp said:
Michael,
Please post exactly what you used that is getting the syntax error.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Michael_100 said:
Lynn, thank you for your reply. I tried your formula but I keep ketting a
syntax error. I have tried to adjust the formula, yet the error persists.
What am I doing wrong?
--
Michael


:

Try IIF(([Avg Of TCF Evaluation] >= .92 And [Avg Of TCF Evaluation] <
.95),
35,IIF( [Avg Of TCF Evaluation] >= .95, 40,SomeOtherNumber))

'SomeOtherNumber' would be for anything that is below .92

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



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]>.95,40,IIf[Avg Of TCF
Evaluation]>.92<.949,35))

I am sure I am just confusing Access. Can someone help me with the
right
formula?
Click to expand...
Click to expand...
Click to expand...
 
L

Lynn Trapp

That'll do it every time. I'm glad you figured out the problem.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Michael_100 said:
I have it Lynn. I was missing the = sign.
--
Michael


Michael_100 said:
Lynn, I tried all of:

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

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

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

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

The first is your suggestion. The rest are variants I have tried.
--
Michael


Lynn Trapp said:
Michael,
Please post exactly what you used that is getting the syntax error.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Lynn, thank you for your reply. I tried your formula but I keep
ketting a
syntax error. I have tried to adjust the formula, yet the error
persists.
What am I doing wrong?
--
Michael


:

Try IIF(([Avg Of TCF Evaluation] >= .92 And [Avg Of TCF Evaluation]
<
.95),
35,IIF( [Avg Of TCF Evaluation] >= .95, 40,SomeOtherNumber))

'SomeOtherNumber' would be for anything that is below .92

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



message
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]>.95,40,IIf[Avg Of TCF
Evaluation]>.92<.949,35))

I am sure I am just confusing Access. 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 5
iif question 13
Calculate between SubReport and Report 1
complicated IIF 3
Access IIF Statement - If field is greater than other field by =/-percentage 0
Problem with IIF statement 5
Max and Min will not display correctly 2
Calculation Using IIf 12

Top