datasheet conditional formatting not working on one field only (1 Viewer)

Statick

Registered User.
Local time
Today, 01:33
Joined
Oct 17, 2019
Messages
13
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
Code:
[Field1] = "Yes"
or
Code:
[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

Super Moderator
Staff member
Local time
Today, 08:33
Joined
Feb 19, 2013
Messages
16,553
this post was moderated - have now approved
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:33
Joined
Feb 19, 2013
Messages
16,553
if [Field1] is a boolean, do not use the quotation marks

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

Statick

Registered User.
Local time
Today, 01:33
Joined
Oct 17, 2019
Messages
13
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

I’m here to help
Staff member
Local time
Today, 01:33
Joined
Oct 29, 2018
Messages
21,357
Hi. Are you able to post a sample version of your db showing the problem?
 

Statick

Registered User.
Local time
Today, 01:33
Joined
Oct 17, 2019
Messages
13
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.

screenshot1.png

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)

screenshot2.png

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

screenshot3.png

and here's the lookup list each field is using

screenshot4.png
 

Statick

Registered User.
Local time
Today, 01:33
Joined
Oct 17, 2019
Messages
13
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.

screenshot5.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:33
Joined
Oct 29, 2018
Messages
21,357
Hi. Thanks for the additional information. What are the data types for the fields [Timings Confirmed] and [Parking Booked]?
 

Statick

Registered User.
Local time
Today, 01:33
Joined
Oct 17, 2019
Messages
13
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

Registered User.
Local time
Today, 01:33
Joined
Oct 17, 2019
Messages
13
they are lookups, as shown in the above attachment screenshot4
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:33
Joined
Oct 29, 2018
Messages
21,357
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:
Code:
Expression is [Combo1549] = "Yes"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:33
Joined
Oct 29, 2018
Messages
21,357
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

Registered User.
Local time
Today, 01:33
Joined
Oct 17, 2019
Messages
13
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

I’m here to help
Staff member
Local time
Today, 01:33
Joined
Oct 29, 2018
Messages
21,357
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

Registered User.
Local time
Today, 01:33
Joined
Oct 17, 2019
Messages
13
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

Registered User.
Local time
Today, 01:33
Joined
Oct 17, 2019
Messages
13
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

I’m here to help
Staff member
Local time
Today, 01:33
Joined
Oct 29, 2018
Messages
21,357
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’m here to help
Staff member
Local time
Today, 01:33
Joined
Oct 29, 2018
Messages
21,357
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

Registered User.
Local time
Today, 01:33
Joined
Oct 17, 2019
Messages
13
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

Registered User.
Local time
Today, 01:33
Joined
Oct 17, 2019
Messages
13
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
 

Users who are viewing this thread

Top Bottom