Data Type Mismatch from blank calculated field.

[Mtyetti2]

I have a report that is based on a query. It calculates expiration dates for employees. For example, a medical examination expires 1 year from exam date. The problem I have is unless I have dates entered for every employee, it gives me a data type mismatch when i view the report.
The dates aren't always required. Is there a way to have the blank calculated expiration dates to read "missing" or something similar in the report? In the query, they show up as #error.

Thank you all so much. Hopefully an easy fix!


Scott

[June7]

An expression to show an alternate text should be possible. Simply: Nz([field],"Missing")

Post query for analysis.

Or provide db. Follow instructions at bottom of my post.

[Mtyetti2]

Still no luck. Used Nz([MedicalExpires], "missing") as the control source in the report. Still kicks back Data Type Mismatch.

[June7]

Odd. The expression works for me.

[ssanfu]

Used Nz([MedicalExpires], "missing") as the control source in the report

Why aren't you using this in the query? That is where I would have the expression.

Plus, it is faster in the query that calculating in a control source of a control in the report.

[Mtyetti2]

Sample database is attached. When you open the report, it gives the Data Type mismatch. I've tried several options, including putting the equation in the Query instead of the Control Source. No avail.
As you can see, the 'medexpires' field is a calculated field.

Thanks for the help!

[June7]

The issue is DateSerial errors on Null or empty string - all number conversion functions do.

MedicalExp: IIf(IsNull([Medical]), Null, DateAdd("m",13,DateSerial(Year([Medical]),Month([Medical]),1))-1)

[Mtyetti2]

June7, you are a genius! That fixed it!!!