MS Access form conditional formatting of textbox based on date not working as expected

Question

I have an Access DB I've been working on that tracks the preconstruction work for each project we are awarded. I've included a form that is a quick glance at the status of some of the initial tasks that need to be performed. The data source is a query that includes the award date and fields that calculate dates for each of the initial tasks. On the form, under each task I have a textbox tied to the calculated date and a checkbox tied to another field in the query indicating if the task is complete or not.

I've added conditional formatting to the textboxes to give a quick visual of what the status of each task is. The first two conditional formats appear to work fine:

1. If there is no start date entered, the background and text are white.
2. If the box is checked as complete, the background and text are green.

Where I am running into issues is with the last four conditional formats:

1. If the date is in the past, the background is red.
2. If the date is in the next week, the background is orange.
3. If the date is between one and two weeks out, the background is yellow.
4. If the date is more than two weeks out, the background is white.

In the screen shot attached, you can see the issues. The date the screen shot was taken was 5/21/2021. One textbox with 5/3/2021 as the date is formatting as being within the next week even though it's in the past. Some dates that are over a month away are formatting as being within the next week.

Any suggestions?05.21.2021 Screenshot

Answer 1

You don't have date values, but expressions interpreted as numbers. Thus:

5/21/2021 = 1.17810607667114E-04 
5/3/2021  = 8.246742536698E-04 

So, go back and make sure you pass date values from the table/query all the way to the form's textboxes.

And, when done, adjust your first condition to:

Value Is Null

Answer 2

Now() returns date + time. Use Date() instead to work with the date only.

Since the rules are applied in sequence, only the first one matching condition applies. Therefore it is not necessary to use Between And.

My suggestion, where white is the default formatting of the textbox, and therefore does not require a condition.

Expression Is [checkbox_name]  ---> green
Value < Date()                 ---> red
Value < Date() + 8             ---> orange
Value < Date() + 15            ---> yellow