Need Help on building an expression, first time expression builder.

[dxqwest]

I have a question on building an expression
through the expression builder in my query or in my report or in VBA. I have a rating field which need to auto populate from a % to objective field. If the % to objective field is 99% or less then the rating field needs to auto populate to "D-Needs Development", if 114.9% or less then the rating field needs
to auto populate to "M-Meeting Standards" and if 200% or less then it needs to auto populate to E-Exceeding Standards". I have tried IIf function in the expression builder through my report but I am not coding it in the right way for Access to give me the result.

 

[jatfill]

If the percentage stored as a number, this should work

I also added an error trap at the beginning for null values...

IIf(IsNull([rating]),"No Data Available",IIf([rating]<100,"D - Needs Development",IIf([rating]>99.9 And [rating]<115,"M - Meeting Standards",IIf([rating]>114.9,"E - Exceeding Standards",))))

change "[rating]" to refer to the appropriate control and see if that works...


[This message has been edited by jatfill (edited 08-22-2001).]

 

[dxqwest]

I tried that expression but I am still getting error messages "Compile err-Expected : )". This is what I have so far, IIf(IsNull([% To Objection]),"No Data Available",IIf([% To Ojection]<100,"D - Needs Development"),IIf([% To Objection]>99.9 and ([% To Objection]<115,"M - Meeting Standards"),IIf([% To Objection]>114.9,"E - Exceeding Standards")


[This message has been edited by dxqwest (edited 08-22-2001).]

[This message has been edited by dxqwest (edited 08-22-2001).]

 

[jatfill]

Is the "% To Objection" field just a number, or is it an actual percentage calculation?

Also, if you're doing this from VBA, it would be done differently... the code I mentioned above would be appropriate as a query expression, or as the control source on a report or form...

[This message has been edited by jatfill (edited 08-23-2001).]

 

[Drew]

Hi,

I haven't checked the logic or even read this post very well but i think the correctly bracketed version would be

IIf(IsNull([% To Objection]), "No Data Available", IIf([% To Ojection] < 100, "D - Needs Development", IIf(([% To Objection] > 99.9) And ([% To Objection] < 115), "M - Meeting Standards"), IIf([% To Objection] > 114.9, "E - Exceeding Standards")))

as a side note you can use iif in VBA it just tends to be a bit slower and a lot more difficult to read

HTH

Drew

[This message has been edited by Drew (edited 08-23-2001).]

 

[dxqwest]

jatfill wrote,

Is the "% To Objection" field just a number, or is it an actual percentage calculation?

Also, if you're doing this from VBA, it would be done differently... the code I mentioned above would be appropriate as a query expression, or as the control source on a report or form...
_________________________

Yes the % To Objection field is an actual percentage calculation calculated in my report form through the control source and that percentage is based on other fields that also have calculation in my report form.

 

[jatfill]

OK I see what's happening then...
since you're evaluating a calculated field and displaying it as a percentage... so the "% To Objection field" needs to be calculated based on the actual numeric value of the "% To Objection" field, not the percentage value (in other words, the actual value of the control you're referring to as Access sees it is the percentage amount displayed divided by 100):


IIf(IsNull([% To Objection]),"No Data Available",IIf([% To Objection]< 1,"N - Needs Development",IIf([% To Objection]>0.99 And [% To Objection]<1.15,"M - Meets Standards",IIf([% To Objection]>1.149,"E - Exceeds Standards"))))

this should work fine on a query, report, or form... good luck

 

[dxqwest]

jatfill,

Thank you so much!!!!! It does work and just in the nick of time because I have to present this database to head district boss with a 15 page presentation on Monday. Again thanks a million to you and the ACCESS WORLD FORUMS!!!