We help IT Professionals succeed at work.

Date field conditional formatting?

zpotok
zpotok asked
on
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...
Comment
Watch Question

Commented:
Couldnt you just set 'none' as the fields default value?
CERTIFIED EXPERT
Commented:
You can change field's value in query or in report from
MyDateField
to:
IIF(isnull(MyDateField),"None", MyDateFIeld)
CERTIFIED EXPERT
Top Expert 2009

Commented:
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.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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
   me.txtYourDate="None"
Else
    me.txtYourdate=YourDate
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.

:-)

Jeff

Author

Commented:
I used
IIf(IsNull(MedicalCertExpireDate),"None",MedicalCertExpireDate)
in query but get 'data type mismatch in criteria expression.



Don't understand Helen's answer...
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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:
DateDetermination:IIf(IsNull(MedicalCertExpireDate),"None",MedicalCertExpireDate)
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
This works just fine for me...
Database95.mdb
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
If my clarifications helped, remember that this was originally posted by als315, so he should get all the points...

Jeff

Author

Commented:
Thanks for your help and thanks Jeff for the clarification.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.