Conditional formatting not working on Dlookup text boxes

Question

I am trying to conditionally format 12 text boxes. They are all linked to a combobox which when a 'Batch' is selected then populates each text box with a number using the Dlookup function.

Me.Textbox = DLast("[Field]", "[Table]", "[Batch]= '" & [Forms]![FormName]![Batch] & "'")
​

I have conditionally formatted all of my text boxes individually, as they each have different limit values and colours.

All of my text boxes are displaying the correct colours for my formatting when I change the batch number in the combo box and thus the numbers in my text box, apart from 2! In those 2 boxes a number in the textbox will change to a colour that does not correspond to the formatting rules I have set. For example:

The conditional formatting rules I have given to one of the boxes (that aren't working) are:

Field value is <= 500 (formatted to be green) Field value is between 500 and 5100 (formatted to be yellow) Field value is > 5100 (formatted to be red)

When a value of ~4000 is looked up, the colour of the text box will be green??

But then on a different batch with a similar number, say 4500 it will be red?

All other boxes with similar rules (just different values) are working correctly. So I don't understand what is wrong with these two boxes. I have tried deleting and reformatting, but nothing.

Could it be anything to do with rule?

When you apply conditional formatting to a lookup field, the condition must be based on the lookup ID, not the value returned by the lookup field."

What does this mean?

Could it have anything to do with using input masks when entering the data into the database?

Answer 1

I've gone with using a query to reference the values from the tables based on the Batch selected in the combobox instead of the Dlookup, and this seems to not be causing problems with conditional formatting of any of the boxes.