Solved Conditional formatting weirdness

[CrustyB]

I'm currently working on a database for product warranty returns, with a test results input form which will colour a field using conditional formatting based on values a user inputs in reference to max and min values from a table. The basic setup of the parts relative to this question is:

* Table "RETURNS_DATA" which generates a returns number as primary key, users input customer/product details, including selecting a product code from table "ITEMS".
* Table "ITEMS" with product codes and test min/max values
* Table "TESTS" with test results, returns number as primary key linked to "RETURNS_DATA"
* Query "TEST_VALUES" which gets return number from "RETURNS_DATA" and relevant product code and test values from "ITEMS" for that return number.
* Form "FRONTPAGE" with combo box to select primary key returns number from "RETURNS DATA".
* Form "SUBFORM_TESTS" as subform within "FRONTPAGE" to input test results into table "TESTS".
* Form "SUBFORM_TEST_VALUE_DISPLAY" as a further subform within "SUBFORM_TESTS" to display min/max test values from query "TEST_VALUES" relevant to the product code associated with the returns number.

This all works as expected and records all the relevant details referenced to the returns number primary key in "RETURNS_DATA". When using "SUBFORM_TESTS" to input test results, the form gets the correct product code and test min/max values for the product code associated with that returns number.

The issues has arisen when I have tried to use conditional formatting to colour the text boxes the user will input their results into in "SUBFORM_TESTS", with the intention for the box to turn red when out of range and green within. I have tried setting this up for the first field (Inlet Pressure) as:

Field Value Is Between/Not Between [SUBFORM_TEST_VALUE_DISPLAY].Form![TESTINMIN] and [SUBFORM_TEST_VALUE_DISPLAY].Form![TESTINMAX] (green and red formatting respectively)

and as value greater than/less than min max with red and green formatting.

Both of these seem to work superficially, however, what I'm finding is this:

TESTINMIN: 750
TESTINMAX: 1000

A value of 800 will display green.
A value of 500 will display red.
A value of 1200 will display red.

All good so far, right? The problem is:

A value of 80 will display green (incorrect)
A value of 50 will display red (correct)
A value of 8 will display green (incorrect).

Am I doing something wrong here? The for the test values in form "ITEMS" are set as Data Field: Number, Double and I have tried various combinations of data fields and settings. I learned to use Access myself last year to build this database for my business and it works well, but there may be something fundamental I'm naïve to here causing the issue.

Any help much appreciated!

 

[MajP]

Conditional formatting has a tendency to treat things like strings.
So the string value of 750 and string value of 1000. Which is going to give some wacky results because 1000 as a string is before 750.
I would try

clng([SUBFORM_TEST_VALUE_DISPLAY].Form![TESTINMIN]) and clng([SUBFORM_TEST_VALUE_DISPLAY].Form![TESTINMAX])

 

[MajP]

Also this could be the other problem where your field is not a number but the field is a string. Double check the fields datatype. It is very common to forget to change the field from short text (default) to number.

 

[CrustyB]

Hi @MajP many thanks for your help.

Unfortunately, using clng doesn't seem to have affected the behaviour.

The datatype in table "ITEMS" for TESTINMIN and TESTINMAX are set as Number, Double.

Is there another way to achieve the result I'm after without using conditional formatting?

 

[MajP]

can you post the database or relevant sample?
If this form is continuous you are kind of stuck with conditional formatting.
If it is single form view then you can use vba to set the background color. Have both the min and max control after update call the format sub you make. In that sub first check that both the min and max are filled in and that the min <= max.

My first guess is still that it is treating these as a string comparison. If I could see it, it would be easier to trouble shoot.

 

[CrustyB]

I'm afraid I can't post the database as it contains customer data and data sensitive to the business. I can see if I can create a sanitised version to show you, but I won't be able to do so immediately. I do appreciate your offer to look at it though!

The forms are all singe view, I believe.

 

[MajP]

Some very stripped down version of the form would be better. Enough to show the problem.
Here is a similar thread where conditional formatting treats a date field like a string. That is why this is my first guess without seeing it.

Solved - Form field conditional background color

I have no idea why this is failing, I simply want to make the background red if the Date is before Today. The results I am getting are all over the place.

www.access-programmers.co.uk

On a report you can use the onPaint event to paint controls in a record. So you can achieve different colors on a continuous report. You can do this on a form, but it only kind of works. As soon as you scroll things get out of wack.

But if this is single record view than most likely you can simply do this in a few lines of code.
In a continuous form with lots of records showing if you change the controls background then it will change for every visible row, that is why you need conditional formatting.

 

[CrustyB]

I've attached a copy of the database with all the tables wiped. There's one record left for testing purposes (returns no 4216).

• On the frontpage, select 4216 in the "SAV" combo box.
• Select the "TEST" tab.
• The field I've been attempting to do this on is "Inlet Pressure" using "TESTINMIN" and "TESTINMAX".
• I've removed the conditional formatting I had set up previously, which was as described above.

Thanks again for all your assistance.

 

[Gasman]

I'm afraid I can't post the database as it contains customer data and data sensitive to the business. I can see if I can create a sanitised version to show you, but I won't be able to do so immediately. I do appreciate your offer to look at it though!

The forms are all singe view, I believe

Use this to obscure your data

 

[Pat Hartman]

You need to fix the data types so they are consistent. You have the "TESTINMIN" and "TESTINMAX". defined as double but you have "Inlet Pressure" defined as short text. Which is it?????? @MajP suggested cLng() because that seems correct looking at your sample. But, he did tell you to check all the fields and make them consistent.

When you compare numeric values, they are compared (logically) by right adjusting them to the decimal point and zero filling. So, if you compare 30.1 to 100.3, the logical compare is
030.1 to 100.3 so the characters are compared left to right.
Char #1 -- 0 to 1. The first item is less so the compare ends with 30.1 being < 100.3

When you compare text values they are left justified and compared left to right so 30.1 is compared to 100.3
Char #1 -- 3 to 1. The first item is larger so the compare ends with 30.1 being > 100.3

When you want to compare text strings for anyting other than = or <> then you need to right justify them and fill them with spaces so the text becomes (I'll use * to represent space) - *30.1 to 100.3 - so space is compared to 1 and that makes 30.1 < 100.3


If you don't actually need decimal places, define the numeric fields as integer or long integer depending on the expected length.

And finally, your naming scheme is really bad.
1. All caps are very hard to read
2. You are using embedded spaces
3. You are not prefixing objects
4. You are not setting the Name property of controls as you make them. You are never going to remember what Command38 is next month.

I know it seems like a daunting task to rename all your objects. At least fix the tables and give the object names valid names instead of the defaults.

You will thank me later. Access does have internal tools that help. But those tools are limited and actually dangerous if you don't understand how they work. Name Auto Correct is sometimes referred to as name Auto Corrupt by experts. I've posted on this a few times. Normally I leave Name Auto Correct turned off but on the very rare instances (I don't make many naming mistakes. I've been doing this a long time) I need to use it I do turn it on.

The think that you need to understand is that for the changes it does propagate, the propagation is not immediate. The propagation only happens the next time the object is opened. So if you are careful to open all objects after you finish the name change (unless of course you KNOW they were not impacted), then you should be safe.

So, if your report is bound to query XYZ and you change the name of that query to qAllAccounts, the change doesn't affect the report until you open the report. At that point Access recognizes that XYZ is invalid and it checks its name change log and finds the new name and updates the report object. The "corrupt" happens if you have layered changes. So you change XYZ to qXYZ and close the database without opening the form/report that uses it. Then you go back and change qXYZ to qNewCustomers but still don't open the affected objects and then turn off the Name auto Correct so the changes never get applied.

 

[CrustyB]

You need to fix the data types so they are consistent. You have the "TESTINMIN" and "TESTINMAX". defined as double but you have "Inlet Pressure" defined as short text. Which is it?????? @MajP suggested cLng() because that seems correct looking at your sample. But, he did tell you to check all the fields and make them consistent.

When you compare numeric values, they are compared (logically) by right adjusting them to the decimal point and zero filling. So, if you compare 30.1 to 100.3, the logical compare is
030.1 to 100.3 so the characters are compared left to right.
Char #1 -- 0 to 1. The first item is less so the compare ends with 30.1 being < 100.3

When you compare text values they are left justified and compared left to right so 30.1 is compared to 100.3
Char #1 -- 3 to 1. The first item is larger so the compare ends with 30.1 being > 100.3

When you want to compare text strings for anyting other than = or <> then you need to right justify them and fill them with spaces so the text becomes (I'll use * to represent space) - *30.1 to 100.3 - so space is compared to 1 and that makes 30.1 < 100.3


If you don't actually need decimal places, define the numeric fields as integer or long integer depending on the expected length.

And finally, your naming scheme is really bad.
1. All caps are very hard to read
2. You are using embedded spaces
3. You are not prefixing objects
4. You are not setting the Name property of controls as you make them. You are never going to remember what Command38 is next month.

Hi and many thanks for your help. Changing the datatype to number in "TESTS" along with clng now has it working correctly.

The way this was being used previously entailed the values input into TESTS sometimes being alphanumerical; I'd been focused on the datatype for ITEMS and didn't think of how TESTS was set up, even after MajP reccomended to check.

As stated previously, I've created this with no previous database experience for my small business, so my knowledge is very limited. I know it's nowhere even close to being perfect, but it's not practicable for me to become an expert before building the database. In the real world business scenario in which it's deployed it fulfils its function even if it isn't well made by the standards of someone who knows what they're on about.

 

[Pat Hartman]

along with clng

The data types need to be the same. although single and integer are both numeric. you will confuse yourself if you don't actually know the decimals are possible. So, remove the clng()

 

[Pat Hartman]

I know it's nowhere even close to being perfect

We're not striving for perfect here. We're striving for something that you will actually be able to work with and expand as your knowledge grows. What you have is a poor foundation. Within two months, you will hate the sight of this app. The sooner you fix at least the two most important things, the fewer objects you end up having to change. The more objects you build, the worse the fix up task becomes and the less likely you are to ever get around to it.

If you're happy, then I won't worry about it. Good luck.

 

[CrustyB]

We're not striving for perfect here. We're striving for something that you will actually be able to work with and expand as your knowledge grows. What you have is a poor foundation. Within two months, you will hate the sight of this app. The sooner you fix at least the two most important things, the fewer objects you end up having to change. The more objects you build, the worse the fix up task becomes and the less likely you are to ever get around to it.

If you're happy, then I won't worry about it. Good luck.

No I get your point entirely; please don't think I'm being dismissive of what you say, it's definitely taken into account.

Thanks again both for your help!

 

[Pat Hartman]

You're welcome and welcome to AWF. We're here for help and advice.