Date field conditional formatting?

I have a report based on a query in which has a date field.  If there is no date I would like the report to display 'None' in the date field.  How best to do this?  Not sure how to write a conditional rule expression if this is the way to go.

Thanks in advance...
Who is Participating?
als315Connect With a Mentor Commented:
You can change field's value in query or in report from
IIF(isnull(MyDateField),"None", MyDateFIeld)
Couldnt you just set 'none' as the fields default value?
Helen FeddemaCommented:
Better wrap the field name with a Format expression, so you have String values for both cases of the Nz function.  You could do this in the report's record source query, which is easier to tweak until it comes out right.  Then just place the calculated field on the report.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Jeffrey CoachmanMIS LiasonCommented:
I like Helen's approach...

...and sometimes a date field is really a text field, so you may want to go further and do this in VBA where you can add validation, error handling, ...etc;

If YourDate="" Or isnull(YourDate) then
end if

Note here that the report control will not have a control source, and that the code sometimes references the field and sometimes the control...

However, this will only work if the report is opened in Print Preview specifically...
Perhaps not a big deal, ...just mentioning it.

But again, the other Experts suggestions first.


zpotokAuthor Commented:
I used
in query but get 'data type mismatch in criteria expression.

Don't understand Helen's answer...
Jeffrey CoachmanMIS LiasonCommented:
You did not explain *How* you tried to use it in a query...?

In the SQL it would be something like this:
SELECT Field1, Field2, IIf(IsNull(MedicalCertExpireDate),"None",MedicalCertExpireDate) AS DateDetermination
FROM Your Table.

In the query grid you would create a new column and use this as the Field:
Jeffrey CoachmanMIS LiasonCommented:
This works just fine for me...
Jeffrey CoachmanMIS LiasonCommented:
If my clarifications helped, remember that this was originally posted by als315, so he should get all the points...

zpotokAuthor Commented:
Thanks for your help and thanks Jeff for the clarification.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.