Conditional Formatting vs Code

[ant1983]

Hi,

I have a report that runs off a query - one of my fields is
numPercentageBooked.

I was going to use conditional formatting to change the backcolour of the
cell as per the below:

0% to 65% = Red
66% to 99% = Yellow
100% = Green
More that 100% = Blue

I noticed though that i cant do more than 3 conditions so that wouldnt work.
Besides, it didnt work even if i just did the 3 conditions so not sure what
i did wrong.

So im guessing my alternative is to write some code and put that on the Load
Event.

I have no idea what to write. Please help

 

[ryguy7272]

Does this get you started
http://forums.techguy.org/business-applications/503589-solved-access-conditional-formatting.html

 

[ant1983]

I wouldnt know how to apply that code plus how do i know what numbers the
colours are?

Ive adjusted as follows but didnt expect it to work (it didnt btw - LOL):

Private Sub Report_Load()
If PercentageBooked Is <=0.65 Then
PercentageBooked.BackColor = 4259584
Else
PercentageBooked.BackColor = -2147483643
End If
End Sub

 

[ryguy7272]

Try something like this:
In your form open event you'll need something like....

Select Case Me.[Field Work]
Case "Red"
Me.YourObjectName.BackColor = 255
Case "Yellow"
Me.YourObjectName.BackColor = 123456
Case "Blue"
Me.YourObjectName.BackColor = 123456
Case "Green"
Me.YourObjectName.BackColor = 123456
End Select
Me.Repaint

Or, send me an email and I'll send you a sample DB that I have.
(e-mail address removed)

 

[JimBurke via AccessMonster.com]

You can use VBA to allow for more than three conditions - here's a link to a
site that has some examples:

http://msdn.microsoft.com/en-us/library/aa139965(office.10).aspx

It might be easier to just code a select statement. From what you said it
sounds like you have a field called numPercentageBooked in a report, and you
want to set it's background color based on it's value. If that's the case,
assuming this field is printed in the detail section, you want to add an On
Format event proc in the detail section. In it you would use a Select
statement something like this:

Select Case numPercentageBooked
Case 0 to 65
Me.Controls("numPercentageBooked").backColor = 255
Case 66 to 99
Me.Controls("numPercentageBooked").backColor = 65535
Case 100
Me.Controls("numPercentageBooked").backColor = 65280
Case > 100 ' you might need to use something like Case 101 to 99999
Me.Controls("numPercentageBooked").backColor = 16776960
Case Else
' not sure what you want to put here
End Select

Not sure if those are the exact shades of the colors you want, but something
along those lines should work. I'm also not sure if you have to use the 'Me.
Controls' format for the assignment - you have to use that sometimes, it's
what I have in my code where I do this. Also, are you sure your percentage
values are always whole numbers? Could you ever have a fractional value like
65.5? If so you'd need to change the values in the Case statements. I hope I
interpreted what you're trying to do correctly!