VBA help

[LOSERMANN]

Hello everyone, i need a bit of help. i have a form (dont we all) that ive built this rather lengthly iif statement expression into the after update property of a textbox control. works great, but.....id like to play a bit with the idea of limiting the number of controls on my form. hence the title of this request...VBA help.

=IIf([txtNC]>=[txtAvgNC150],&quot;Outstanding&quot;,(IIf([txtNC] Between [txtAvgNC150] And [txtAvgNc125],&quot;High Acceptable&quot;,IIf([txtNC] Between [txtAvgNc125] And [txtAvgNc75],&quot;Acceptable&quot;,IIf([txtNC] Between [txtAvgNc75] And [txtAvgNc50],&quot;Low Acceptable&quot;,IIf([txtNC]<[txtAvgNc50],&quot;Unsatisfactory&quot;,&quot;&quot)))

thanks in advance folks......

 

[KenReay]

Hi

Do not understand your question?, if it helps you can find the number of controls on you form using Me.Controls.Count Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JerDale]

Your code:
=IIf([txtNC]>=[txtAvgNC150],&quot;Outstanding&quot;,(IIf([txtNC] Between [txtAvgNC150] And [txtAvgNc125],&quot;High Acceptable&quot;,IIf([txtNC] Between [txtAvgNc125] And [txtAvgNc75],&quot;Acceptable&quot;,IIf([txtNC] Between [txtAvgNc75] And [txtAvgNc50],&quot;Low Acceptable&quot;,IIf([txtNC]<[txtAvgNc50],&quot;Unsatisfactory&quot;,&quot;&quot)))
*********************************************************

Let me offer you a tip on whittling down your code. You are comparing, in one statement, a value of one control with values of other controls (or maybe variables) that assign text based on scores in the ranges of 150 or more, 125+, 75+ 50+, and under 50. Your use of &quot;Between&quot; works but is not perfect logic: a score of 150 would pass both the first and second conditions although Access would pick only the first condition (>=[txtAvgNC150]). I would use the following syntax, which I have tested, to assign text to a control based on a score.

= IIf(txtNC >= 150, &quot;Outstanding&quot;, _
IIf(txtNC >= 125, &quot;High Acceptable&quot;, _
IIf(txtNC >= 75, &quot;Acceptable&quot;, _
IIf(txtNC >= 50, &quot;Low Acceptable&quot;, _
IIf(txtNC < 50,&quot;Unsatisfactory&quot;,&quot;No Score&quot))))

I hope this is helpful.