FormatCondition Numeris/alpha testing

[Dominic Vella]

Hi

I'm developing code to build Matrix's and is already working, however I'd
like to include some conditional formatting to highlight Low, Medium, High,
Critical issues.

I've added a function which updates a Control (eg. text or combo box) with
Conditional Formatting, but it doesn't seem to be working properly.
Here is an example of 'similar' code I've written.

----------------------------------------------------------------------------
With Me.cboTest.FormatConditions
.Delete
.Add acFieldValue, acBetween, 7, 12
' The real code derives Add arguments from
' recordsets, so there may be variances
' on expression1 and expression2

.Add acFieldValue, acBetween, 13, 18
.Add acFieldValue, acGreaterThanOrEqual, 19
End With

Me.cboTest.BackColor = 255 ' Red
Me.cboTest.FormatConditions(0).BackColor = 65535 'Yellow
Me.cboTest.FormatConditions(1).BackColor = 16711680 'Blue
Me.cboTest.FormatConditions(2).BackColor = 65280 'Green
----------------------------------------------------------------------------

For some reason, the conditional formatting
1 ends up Red
2 to 7 ends up Yellow (Condition 0)
8 to 9 ends up Green (Condition 2)
10 to 11 ends up Red
12 to 25 ends up Yellow (Condition 0)

Can someone explain what's happening? Is it in some alphabetical testing
mode instead of Numerical testing? Is there a way to switch testing modes
from Numeric to Alphabetical?

 

[Dirk Goldgar]

Hi

I'm developing code to build Matrix's and is already working, however I'd
like to include some conditional formatting to highlight Low, Medium,
High, Critical issues.

I've added a function which updates a Control (eg. text or combo box)
with Conditional Formatting, but it doesn't seem to be working properly.
Here is an example of 'similar' code I've written.

----------------------------------------------------------------------------
With Me.cboTest.FormatConditions
.Delete
.Add acFieldValue, acBetween, 7, 12
' The real code derives Add arguments from
' recordsets, so there may be variances
' on expression1 and expression2

.Add acFieldValue, acBetween, 13, 18
.Add acFieldValue, acGreaterThanOrEqual, 19
End With

Me.cboTest.BackColor = 255 ' Red
Me.cboTest.FormatConditions(0).BackColor = 65535 'Yellow
Me.cboTest.FormatConditions(1).BackColor = 16711680 'Blue
Me.cboTest.FormatConditions(2).BackColor = 65280 'Green
----------------------------------------------------------------------------

For some reason, the conditional formatting
1 ends up Red
2 to 7 ends up Yellow (Condition 0)
8 to 9 ends up Green (Condition 2)
10 to 11 ends up Red
12 to 25 ends up Yellow (Condition 0)

Can someone explain what's happening? Is it in some alphabetical testing
mode instead of Numerical testing? Is there a way to switch testing modes
from Numeric to Alphabetical?

I guess that either your combo box is unbound, or it's bound to a text
field, so text comparisons are being made. If you want a numeric
comparison, you need to bind it to a number field, or else use expressions
in your format condition to convert the text field value to a number for
comparison.

 

[Dominic Vella]

Even if the results are testing Alphabetically, the results still don't make
sense to me.

My Matrix ComboBox has 2 columns and first column is 0 width, bound column
is 1. I used CDbl() in the rowsource SQL in the first row's column to
ensure it is numeric.

My FormatCondition.Add function now looks more like this:
.Add acFieldValue, acBetween, CDbl("7"), CDbl("12")

I used CDbl() in the rowsource SQL in the first row's column to ensure it is
numeric.

Could it be that the condition is testing column 2? Should I use dummy
numbers to make it think Numerical and then use .Modify() with accurate
data?

I'm happy to send the matrix database to an MVP in order to get an solution.
Thanks in advance.


Dom

 

[Dominic Vella]

Well, it seems the problem has something to do with the ComboBox as I had no
problems with the TextBox.
So, this means I will:
- make the background of the ComboBox transparent,
- Apply the ComboBox.RowSource as required
- Put the TextBox behind the ComboBox and apply the Conditional Formatting
- Set the TextBox.ControlSource to = CDbl(ComboBox.Column(0))

It works, but it seems Conditional Formatting for ComboBox's could have some
improvements.

Still, if anyone has a better fix, I'd welcome it!!! Silly me for wanting
to make a Matrix editor in the first place.


Dom

 

[Dirk Goldgar]

Even if the results are testing Alphabetically, the results still don't
make sense to me.

My Matrix ComboBox has 2 columns and first column is 0 width, bound column
is 1. I used CDbl() in the rowsource SQL in the first row's column to
ensure it is numeric.

It's not the RowSource you need to be concerned about; it's the field (if
any) that the combo box is bound to. What is the ControlSource of the combo
box? Or is it unbound (no ControlSource)?

My FormatCondition.Add function now looks more like this:
.Add acFieldValue, acBetween, CDbl("7"), CDbl("12")

There's no meaningful difference between that and:

.Add acFieldValue, acBetween, 7, 12

I'm happy to send the matrix database to an MVP in order to get an
solution.

You can send it to me, if you like, but I'd prefer first to see if we can
solve the problem entirely in the newsgroup. If you'd please answer the
questions I asked above, I think we can sort it out.