datasheet conditional formatting not working on one field only

[Statick]

I have a number of fields in my table which are simple lookups with two items in the lookup : "yes" and "no". I've done this rather than use a checkbox, because it's very important that I can do conditional formatting on the columns when these are viewed in a datasheet, which isn't possible using checkboxes.

In all of the columns I have implemented the formatting using a very simple expression as follows

[Field1] = "Yes"

or

[Field1] = "No"

which colours the background of the cell red or green accordingly. This is working well for all of my fields, except for this one which fails. Both expressions fail so no rules are used to format the cell. Yet in that same field, if I set the conditional formatting to directly check the value against either "yes" or "no" it works fine. It only doesn't work if I use an expression which references the field. But I need to use an expression as I need to add another condition to the expression (which is already working fine in all the other fields).

I've recreated the rules a dozen times, using the expression builder, there are no typos here. I've checked the lookup list over and over. The broken field is literally identical to the others that all work fine. It also fails the same way if I put the same formatting rules into a different field, and reference this broken field in those rules. So it looks like it's failing to read the field, or the values are being read differently somehow.

Is there any kind of "debug" mode where I can see what Access thinks the value of the field is when it's running the expressions?

 

[CJ_London]

this post was moderated - have now approved

 

[CJ_London]

if [Field1] is a boolean, do not use the quotation marks

booleans are numbers: -1=true/yes, 0=false/no

 

[Statick]

it's not boolean, it's a lookup from a list containing two records "yes" and "no"

the same identical expression works for formatting on 4 other fields, exactly the same way, but does not on this field

 

[theDBguy]

Hi. Are you able to post a sample version of your db showing the problem?

 

[Statick]

here's the datasheet with the columns that are supposed to format. the columns "crew booked" "timings" and "rentals" are all working correctly (ignore the unformatted "merch" column as this has different rules and is working as it should). The "parking" column is supposed to be formatted as well, but it is not working.



here's an example of the rules which are working correctly for the "timings" column (these same rules are applied on about 5 different fields and they all work correctly)



here's the rules on the column which isn't working. it's the same rule!



and here's the lookup list each field is using

 

[Statick]

and, if I change the rule on the one that isn't working, so instead of the expression it simply checks the field value, then it works. the following shows this change for "no" and it works fine, formatting the "no" results correctly.

 

[theDBguy]

Hi. Thanks for the additional information. What are the data types for the fields [Timings Confirmed] and [Parking Booked]?

 

[Statick]

really appreciate any insight into this as it's got me stumped

I've tried deleting and recreating the broken field entirely, and it still happens

 

[Statick]

they are lookups, as shown in the above attachment screenshot4

 

[theDBguy]

and, if I change the rule on the one that isn't working, so instead of the expression it simply checks the field value, then it works. the following shows this change for "no" and it works fine, formatting the "no" results correctly.

View attachment 77336

Ah, I just noticed something. See if this makes a difference:

Expression is [Combo1549] = "Yes"

 

[theDBguy]

they are lookups, as shown in the above attachment screenshot4

Hi. A lookup field should still have a data type, but it may not matter now. Please try the change I suggested above.

 

[Statick]

ahh right got it - I just typed the text into the wizard, so I'm guessing it chose short text for the datatype

I hadn't noticed the column was named that way in the datasheet, I've just tried using the column name in the expression rather than the field name, but it's not helped. Still not formatting!

 

[theDBguy]

ahh right got it - I just typed the text into the wizard, so I'm guessing it chose short text for the datatype

I hadn't noticed the column was named that way in the datasheet, I've just tried using the column name in the expression rather than the field name, but it's not helped. Still not formatting!

Okay, if it didn't work, then we may have to see a copy of your db for further investigation. Otherwise, you might try creating a new lookup column and try it out. If it works, you can replace the old one with it.

 

[Statick]

now that this naming issue has been highlighted, the same discrepancy (the datasheet column being "combobox01234" or whatever) is also there on some of the fields that do work

 

[Statick]

I did already delete [Parking Booked] entirely from the database, and create it again from scratch. Issue remains. Also get the same issue on any other field that references [Parking Booked] for the formatting (I originally intended to format both of those parking columns based on this field, and they both fail)

 

[theDBguy]

now that this naming issue has been highlighted, the same discrepancy (the datasheet column being "combobox01234" or whatever) is also there on some of the fields that do work

Unfortunately, with all these inconsistencies, it is hard for us to troubleshoot the problem if we can't see it.

 

[theDBguy]

I did already delete [Parking Booked] entirely from the database, and create it again from scratch. Issue remains. Also get the same issue on any other field that references [Parking Booked] for the formatting (I originally intended to format both of those parking columns based on this field, and they both fail)

So, are you able to post a sample copy of your db? You can take out any data, if you like.

 

[Statick]

oh it seems your earlier suggestion has worked - I stupidly tested it on "no" when none of the fields actually contain the result "no"...

So by referencing the column name (which I've renamed from combobox to "Parking Booked Col") has worked, but referencing the database field itself does not - is there a reason for this?

 

[Statick]

yeah I'm happy to share my database if it's still needed, although it looks like we're getting somewhere now! Thanks so much for the help, this has puzzled me for several days now