IIF statement in Detail not working 1

[gretsch]

Im using this statement in the Control Source of a report field
=IIf([IndPTP]>125,"YES","NO&quot

[IndPTP] is a field on the report not in the underlying query. It always results in "NO" when I run the report,

HELP

Gretsch

 

[dhookom]

Is IndPTP a text field or numeric? Is it the result of a calculation in the report's record source? You might want to try:
=IIf(Val([IndPTP])>125,"YES","NO&quot
If that doesn't work, then check your data.

Duane
MS Access MVP

 

[gretsch]

It is a calculated field, numeric (percent), I tried the Val made no diff, always comes out as Not True...
Gretsch

 

[dhookom]

Ok, so it's a percent? Did you try:
=IIf([IndPTP]>1.25,"YES","NO&quot


Duane
MS Access MVP

 

[PaulBricker]

Data type shouldn't make any difference. It's probably the fact that it's a calculated control you are testing. You might try doing the calculation directly in your expression like this

=IIf([Field1]/[Field2] > 125, "Yes","No&quot

Where [Field1]/[Field2] is the calculation you are performing in the textbox IndPTP


Paul

 

[gretsch]

OK great its wrking with the 1.25... next question:
I am a VB programmer with very limited access reports experiance, how can i make this same IIF statement a nested one? eg:
if the Val is between 1.25 and 1.50 make the result A
if the Val is between 1.51 and 1.75 make the result B
if the Val is between 1.76 and 2.00 make the result C
if the Val is between 2.01 and 2.25 make the result D
if the Val is > 2.26 make the result E.

Not sure how to do this within the Control source of a field in a report..

HELP (again)

 

[dhookom]

Something like this might work:
=Choose( (Val-1)\25,"A","B","C","D","E&quot
Make sure you use the "\" rather than "/".

Duane
MS Access MVP

 

[dhookom]

My previous response should have been "\.25" not "\25".

However, I would probably create a lookup table with ranges and results. This would make the solution more "data driven" rather than "hard-coded".

Duane
MS Access MVP

 

[CosmoKramer]

Duane,

I have to give you a star for the ingenuity of your Choose statement. But, there are a couple problems:

You do need the "/.25" to return the floating point result.

And unfortunately it doesn't account for the "> 2.26" criteria.

I agree a lookup table is the best bet in this situation.

Hoc nomen meum verum non est.

 

[dhookom]

As you can see my first try was wrong since I use 25 rather than .25. The integer divide would have worked with
=Choose( ((Val-1)*100)\25,"A","B","C","D","E&quot

And yes, values beyond 2.26 would have to be addressed with a longer expression such as
=IIf(Val>2.26,"E", Choose( ((Val-1)*100) \25, "A", "B","C","D","E&quot

My reply was a push in a direction and should have been more complete. In a similar thread I also suggested the Switch() function which is easier to maintain than multiple IIf()s.

Duane
MS Access MVP