How to apply conditional formatting to date values in a calculated text field

[athanera]

Hi! I am fairly new and appreciate any help here! I am trying to apply conditional formatting in a form to highlight any records in a field called ExpirationDate2 that will expire before 11/30 of the current year. The expression I am using is: Value < CDate("11/30/" & Year(Date$()))

While that expression is working just fine in other date fields, ExpirationDate2 is formatting dates incorrectly. I suspect this is because it is a text field that is actually calculated in a query, since not all records expire (I made a separate field for those called "Open"). This is the expression from the query:
ExpirationDate2: IIf([Open]=False,[ExpirationDate],"Open")



Is there a way to specify that conditional formatting should only apply to certain values (those that look like dates, even though they are stored as text)?

[June7]

Maybe use the IsDate() function.

[athanera]

Thanks for the reply! That sounds like what I need- but the expression I made is still not picking out the right dates:
IsDate([ExpirationDate2]) And ([ExpirationDate2]<CDate("11/30/" & Year(Date$())))

Any reason why this would not work? Some of the right dates are highlighted, but so is 1/1/2023...

[June7]

Did you select Expression Is?

Try:

IIf(IsDate([ExpirationDate2]), [ExpirationDate2]<CDate("11/30/" & Year(Date())), False)

[athanera]

Yep, Expression Is was selected. I had tried the IIF expression as well- but it does not result in any cells being highlighted. Seems like it should work. Running IsDate all by itself correctly highlighted all the dates, and the rest of the expression was used successfully elsewhere in the database. For fun, I built another form in case corruption was the cause of this funkyness, but no dice there either. I wonder what else is at play.

[June7]

I tested expression on data that mixes number and string values in a text field. Used IsNumeric. It works.

My suggestion does have a typo. In case you didn't catch, missing /. I corrected the post.

If you want to provide db for analysis, follow instructions at bottom of my post. Identify object involved.

[athanera]

This database is chock full of personal information, so I am downsizing and disguising enough of it to post. Looks like it won't be ready until Monday. I appreciate your time and have a wonderful weekend!

[athanera]

Found the solution! I definitely don't understand why, but when I added a new field to the form, and used the exact same expression that I used in the query, the conditional formatting--with my initial expression--works! So, instead of adding the calculated field from a query, I just performed that calculation in the control source of a new field... and now Access seems to recognize dates just fine, even though the expression gives a mix of dates and text. So strange.