Conditional Formatting working on text boxes in report sometimes

[new_2_prog]

This makes absolutely no sense:

I have a field on a report in the detail section called "QtyBlankstoCut", next to it are 2 more fields "Min1", "Max1" so what I want to do and did in Excel is if
QtyBlankstoCut > Max1 turn yellow
QtyBlankstoCut < Min1 turn red
QtyBlankstoCut between Min1 and Max1 turn green

Look at the report sometimes it's right and others not, why?

I tried reordering, no difference, tried vb, same results, made sure the text boxes I was referencing were named correctly, am I missing something obvious?

Thanks!!

 

[pbaldy]

The sort order would be irrelevant. First thing that comes to mind since it sometimes works is whether one or more of the fields is text (or being seen as text). If so, you're going to get an alphabetic comparison rather than a numeric one. That would make 3 greater than 22.

If you run the underlying query, do the fields align right or left?

 

[new_2_prog]

The fields in question align right in the query.
I did notice in the report and query looking at the properties that I can choose standard for the format for QtyBlankstoCut but I can't for Min1 or Max1, does that mean anything?
Thanks.

 

[pbaldy]

It seems suspicious. You can try the appropriate conversion function (CCur, CLng, etc):

CLng(QtyBlankstoCut) > CLng(Max1)

to force them to be treated as numbers.

 

[new_2_prog]

BEAUTIFUL!! Worked!!
Now I see where you are coming from, I determined my min1 and max1 in a seperate query, it took the max number of blah so I ran that query and yes it aligned left so I changed the property in that query for Min1 and Max1 to standard and then opened query #2 that used query #1 and tried to format the property there and got nothing in the drop down yet when I ran query #2 the numbers aligned right just like the QtyBlankstoCut but I guess it still wasn't reading it as a number. Pain, lol.
That worked great to force them when all else failed, thanks again!!

 

[pbaldy]

Happy to help!