Percentage Calculation - Vary Colour On Result

[DAmoss]

Hi,

This is small but annoying problem that I just can't seem to get my head around, help! I have the following in my 'PcentNINO' Textbox control source:

=Format(Count([NINO])/[TotMisBensRecs],"0.00")+0

This gives me a calculated percentage figure that shows up in the above mentioned PcentNINO textbox, no problem!

But I want to change the PcentNINO field textcolour depending on the value returned to one of the following:

LightRed: <100%
LightGreen: =100%

How and in which form event do I put my code to ensure that the calculation is done first before it changes the colour?

Unfortunately, I can't use 'Conditional Formatting' as this is an old Access 97 database that isn't going to be upgraded anytime soon!

Thank you in advance

 

[TheAceMan1]

Howdy DAmoss . . .

No event. Your looking for [blue]Conditional Formatting[/blue] with the two conditions you've prescribed:

[blue]Condition1:
   Expression Is [PcentNINO] < 100

Condition2:
   Expression Is [PcentNINO] = 100[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Remou]

Have a look in this link for Color Con:

http://www.mvps.org/access/resources/downloads.htm

"From Kevin Gray. Another demo on conditional formatting in continuous forms. Access 97 version.

 

[DAmoss]

TheAceMan1:

You didn't read all of my post which said:

'Unfortunately, I can't use 'Conditional Formatting' as this is an old Access 97 database that isn't going to be upgraded anytime soon!'

 

[TheAceMan1]

DAmoss . . .

If your saying your currently using 97 then you should upgade as soon as possible! . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DAmoss]

TheAceMan1:

No, I'm saying the company I work for use 97, and they have no intention of upgrading it in the near or fa future.

So back to my original question ... I take it there is no way of doing this?

Remou:
I had a look at the link you supplied, but it doesn't do what I want.


Thinking about it, it may be better if I calculate all the various totalss in the form module rather than in the actual fields, then pass the results to the unbound fields and then colour them accordingly depend on the value. Should have thought of this earlier!

 

[TheAceMan1]

DAmoss . . .

If its an [blue]continuous form[/blue], you need [blue]conditional formatting[/blue] ... no way out!

I wish I had better news ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Vyurr]

Hi DAmoss,

I'm not sure this will serve your purpose, as my VBA skills are rudimentary at best, but could you not just code a simple IF statement into the forms OnCurrent event?

Something like:

If Me.PcentNINO.value = "100%" then
Me.PcentNINO_label.forecolor = <whatever colour>
End if

Like I said, I'm not well versed in VBA, but that's how I got around a similar issue.

V

 

[DAmoss]

Hi Vyurr,

I came up with the following solution to help me colour my required percentage fields, no conditional formatting was required in the end. it basically does what you have mentioned in your post.

Thank you for your help.

Private Sub SetTxtFColRange(vInField As Variant, vUpperNum, vColourMax, vColourMin As Integer)

With vInField
If (.Value = vUpperNum) Then
.ForeColor = QBColor(vColourMax)
Else
.ForeColor = QBColor(vColourMin)
End If
End With

End Sub