Conditional Formatting works only randomly

[Reno911]

I have a Report “SITE_VISITS” with a Text Box, “Text15”, formatted as a Short Date, that uses Conditional Formatting to turn Red when “TA_START_DATE” occurred 180 day ago.

“TA_SITE_VISITS” Query is the Record Source for the Report. Text 15 displays the most recent date for the site visit by using:

Text15=DMax("[TA_START_DATE]","TA_SITE_VISITS","[PWSID_FDS]=" & [PWSID_FDS]), PWSID_FDS is the primary key for each record. All of this seems to work just fine.

Conditional Formatting is set on Text15 by using Expression Is ………..[Text15]<(Date()-180). This color coding works for 95% of the records displayed. However, it randomly does not change to red for 1-2 records in the list. The exact number varies. In other words, a TA_START_DATE that was 300+ days ago does not turn red when it should. With each opening and closing of the report, the randomness changes, correctly Conditional Formatting those records that were previously not working, and randomly other records that are clearly “past due” do not turn red.

Any suggestions/feedback would be greatly appreciated. Thank you.

 

[Gasman]

Wow, joined over 4 years ago and this is your first message?
I would add the DateValue into the report to compare the actual date number.

I was initially thinking it could be a USA/UK date format issue, but the years are too far in the past.
Could also upload a DB with some sample errant data.
Use this to obscure your data

 

[DHookom]

I would first change the name from Text15 to txtLastVisitDate try an expression:
DateValue([txtLastVisitDate])<(Date()-180)

 

[Reno911]

Thanks for your reply. I know, off to a slow start here.
I should point out, the records are displayed in the Footer (see attached). Otherwise, if it was just in the Detail section, the DMax was not working.

 

[Reno911]

I would first change the name from Text15 to txtLastVisitDate try an expression:
DateValue([txtLastVisitDate])<(Date()-180)

Thanks for your reply. I made those changes and find the results are still random....the only exception being the first record (Battle Mountain CSS) is now always not Conditionally Formatted (i.e. not turning red). That's something different. As with before, everytime I open and close the report the results change.

 

[DHookom]

It looks like you aren't using the detail section so you could create a report with a record source of:

SELECT PWSID_FDS, Max([TA_START_DATE]) As MaxStartDate 
FROM TA_SITE VISITS 
GROUP BY PWSID_FDS;

The conditional formatting should then be more consistent.

 

[Reno911]

It looks like you aren't using the detail section so you could create a report with a record source of:

SELECT PWSID_FDS, Max([TA_START_DATE]) As MaxStartDate
FROM TA_SITE VISITS
GROUP BY PWSID_FDS;

The conditional formatting should then be more consistent.

Got it to work using the Record Source shown above, however, still see the random Conditional Formatting issue as before (maybe slightly less random. Now, only the first record doesn't format). Thanks for teaching me something new! It's ok, I can live with it as-is. Thank you for your assistance.

 

[DHookom]

Create a query with the two tables and join on the primary/foreign key fields. Group by PWSID_FDS and SYSTEM_NAME and use Max on the TA_START_DATE.

 

[Reno911]

Got the new record source to work. Thanks again