Conditional Formatting Not Working

[jordankoal]

I hate a field that displays a date on a form.
I set the conditional formatting to be
Value is Between Date() and Date() + 14

It would only apply the formatting for dates that were equal to the date and +5 days.

I then tried Between Now() and Now() + 14
Same results

I then tried Between Date() and DateAdd("d",14,Date())
No change

I checked the property for the box and the back style is normal.

I am making the back color a light red. I tried to change the fore color red. Nothing.

I displayed what Date() was giving me in a text box control and I displayed what Date() + 14 was giving me in a text box control. They both were showing the correct dates, but my date, which was in between them, was not changing colors.

Any ideas?

 

[maw230]

How are you inputting your date formulas into the conditional formatting box?

 

[jordankoal]

Yes. in the conditional formatting box it has Value is and then Between and I input Date() and then in the second box Date() + 14

 

[jordankoal]

I even tried putting this into the Expression Is box:
[90% Check]>=Date() And [90% Check]<=(Date()+14)

No matter which way I do it it will not give me dates that are 14 or less highlighted. I checked and it only goes up to dates that are 5 days or less and highlights them.

 

[maw230]

I even tried putting this into the Expression Is box:
[90% Check]>=Date() And [90% Check]<=(Date()+14)

No matter which way I do it it will not give me dates that are 14 or less highlighted. I checked and it only goes up to dates that are 5 days or less and highlights them.

The formula works just not with any dates higher than 5 days??

 

[jordankoal]

Yes, If the date is >=Date() or <=Date()+5 it works.

 

[CJ_London]

do you have any other conditional formatting rules for this control? is this the only one?

and just to confirm, the field type is a date? it's just that [90% Check] seems an unusual name for a date

 

[maw230]

I would create a dummy table with a date field and add it to a form. Do the same thing you're doing now and see if you get the same results.

 

[jordankoal]

do you have any other conditional formatting rules for this control? is this the only one?

and just to confirm, the field type is a date? it's just that [90% Check] seems an unusual name for a date

There are no other formatting rules for that control. There are other rules for other controls on the report.

I made sure it was a date by putting =Format([90% Check], "Short Date") for the data.

 

[jordankoal]

I have a query. This query is working off of a project information table. In the table the project has a phase. In the query I have the 30% check expression that says if the project phase = "30%" then make this expression equal the date in the 30% Due Date field. I do the same thing for the other project phases.

When I put the 30% check on the form as a control and go to the format pull down in the format tab of the property sheet it doesn't give me an option of anything. If I add the 30% due date and do the same thing it gives me the option of changing the type of date. Could this be the problem?

I went back to the query and formatted the expression as a "Short Date" and then re added the 30% check to the report but it still won't highlight the correct dates

 

[jordankoal]

I would create a dummy table with a date field and add it to a form. Do the same thing you're doing now and see if you get the same results.

I went and added in the 90% Due Date control, which has the same date as the 90% check and did the same conditional formatting rules and it seems to work.

 

[Minty]

I would check your field names and importantly your control names and make sure they are different.
I would also move away from your names with % in them as it's likely to cause you problems if not now it will later. Call your 30% calculated date field some thing like dte30Check. Then call the form control txt30Per . this way you will definitely know which you are referring to.

 

[maw230]

I have a query. This query is working off of a project information table. In the table the project has a phase. In the query I have the 30% check expression that says if the project phase = "30%" then make this expression equal the date in the 30% Due Date field. I do the same thing for the other project phases.

When I put the 30% check on the form as a control and go to the format pull down in the format tab of the property sheet it doesn't give me an option of anything. If I add the 30% due date and do the same thing it gives me the option of changing the type of date. Could this be the problem?

I went back to the query and formatted the expression as a "Short Date" and then re added the 30% check to the report but it still won't highlight the correct dates

Sounds like it may be because it's an expression and not an actual field per se, but I don't know for sure. You can easily test that though.

After you format the expression are you given the same options in the property sheet as [30% due date]?

 

[jordankoal]

I did go in the query and change the format of the expression to equal short dates but it didn't make a difference.

 

[Minty]

Formatting an expression does not change it into a date. It only displays it like a date. This means when you compare things you aren't probably comparing like with like.

Go back a stage - open your query and try restricting your date fields using the same criteria - maw320 suggested a similar path.

Also try displaying your date + 14 and see what result you get...

 

[jordankoal]

I go to the report and put in another control as a text box that has =[90% Check] + 14 and get #Type! as a result on the report.

 

[CJ_London]

I made sure it was a date by putting =Format([90% Check], "Short Date") for the data.

assuming it was a date before, format converts it to text

 

[Minty]

I go to the report and put in another control as a text box that has =[90% Check] + 14 and get #Type! as a result on the report.

This indicates that as we suspected you aren't dealing with a date but a text field. Try creating a calculated field in your query that is something like

14Added: dateadd("d",14, [Your original field])

If this fails your original field is not being treated like a date. If it works you are halfway to solving your problem.

 

[RainLover]

A date is a Date regardless of Short Date etc.

There is no real reason to format the Date as far as I can see.

Try doing this in VBA rather than using the Format tool.