Conditional Formatting in Access Using Different Fields

Question

I have a form with an embedded subform in it. I want to use conditional formatting to change a field in the overall form to have a pink background color if a field in the embedded subform is True (1). So far I have: Format only cells where the: Expression Is:

Forms!frmSubForm!TextField.Value="True"

But this is not working. Any Ideas as to what I am doing wrong? The value that I am checking for "True" comes from a dbo table from SQL, where the value is 1 if true and 0 if false. Access seems to automatically convert 1's to True and 0's to False in form view. Still, I am not sure if the check for "True" is correct, or if it should check for "1" or 1...

Johnny Bones · Accepted Answer · 2015-11-24 19:28:06Z

1

It depends on the field type in SQL. If it's a bit, try something like this:

SELECT CAST('TRUE' as bit) -- RETURN 1
SELECT CAST('FALSE' as bit) --RETURN 0

If it's varchar or int, that might work as well.

answered Nov 24, 2015 at 19:28

Johnny Bones

8,4927 gold badges61 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

wbrugato Over a year ago

I just checked, and the data is indeed of bit type. Is there a way to account for this in the expression for the conditional formatting? I don't want to change anything in the SQL dbo.

Johnny Bones Over a year ago

Then you'll need to do conditional formatting where Forms!frmSubForm!TextField.Value="1" since it's already a bit. Double-check the linked table in design view in Access, though, because I'm pretty sure a bit field gets converted to Yes/No in Access.

wbrugato Over a year ago

yeah, good call. It got pulled in as a Yes/No in Access. Will the check for "1" still work?

Johnny Bones Over a year ago

If it's Yes/No, then -1 = True and 0 = False (or I might have that backwards). It's definitely -1 and 0, though.

Andre · Accepted Answer · 2015-11-24 19:35:54Z

1

The safest way is probably to check for <> 0, this avoids the 1 / -1 / True confusion.

Forms!frmSubForm!TextField.Value <> 0

answered Nov 24, 2015 at 19:35

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Collectives™ on Stack Overflow

Conditional Formatting in Access Using Different Fields

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related