Change Color of field based on a Check box

[Guest]

I have a database where the tables were created in SQL Server. My 2 fields
in question are: Date field and a Completed Field; The Completed field is an
int data type. In access I put a checkbox on a form and its control source
is the Completed field. If the checkbox is NOTchecked and there IS a date in
the Date field, I would like the Date to appear Red. I put in conditional
format on the date field as follows: [CkBoxCompleted]<>1 and chose Red. It
does not seem to be working. Any suggestions?

 

[Rob Parker]

Is the value of the Completed field set to 1 for true? If it's being set
via a checkbox on an Access form, it will have a value of -1 for true, and 0
for false.

Also, you do not appear to have included any test for whether the Date field
is null, so the conditional formatting will not take that into account. You
will need an expression like:
([CkBoxCompleted]<>-1) AND NOT (IsNull([YourDateFieldName]))
in the conditional formatting dialog. Note that your date field should NOT
be named "Date", since that's a reserved word.

HTH,

Rob

 

[Guest]

Thank you this did work. The only thing I had to do beside what you told me
was to default the value of the checkbox to "0" otherwise it is null. Thanks
again.

Is the value of the Completed field set to 1 for true? If it's being set
via a checkbox on an Access form, it will have a value of -1 for true, and 0
for false.

Also, you do not appear to have included any test for whether the Date field
is null, so the conditional formatting will not take that into account. You
will need an expression like:
([CkBoxCompleted]<>-1) AND NOT (IsNull([YourDateFieldName]))
in the conditional formatting dialog. Note that your date field should NOT
be named "Date", since that's a reserved word.

HTH,

Rob

I have a database where the tables were created in SQL Server. My 2 fields
in question are: Date field and a Completed Field; The Completed field is
an
int data type. In access I put a checkbox on a form and its control
source
is the Completed field. If the checkbox is NOTchecked and there IS a date
in
the Date field, I would like the Date to appear Red. I put in conditional
format on the date field as follows: [CkBoxCompleted]<>1 and chose Red.
It
does not seem to be working. Any suggestions?