Display Missing or N/A for Blank Date Fields in Microsoft Access
Question details
Display a replacement message when a Microsoft Access date field is blank.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- The user wants a table, query, or form to show Missing or N/A instead of a blank date.
- Observed behavior
- A Date/Time field cannot directly store or display replacement text as its date value.
Identify the Date/Time field and the query or form where you want the message to appear. Replace the example field names in the expressions with your own.
Show a replacement message in an Access query
Use a calculated query field when you need a readable date or a message in query results.
In the Navigation Pane, right-click the query and select Design View. Alternatively, select Create > Query Design and add the table containing the date field.
In an empty Field cell in the design grid, enter: DateDisplay: IIf(IsNull([DateField]),"Missing",Format([DateField],"mm/dd/yy")). Replace DateField with your field name and Missing with N/A if preferred.
Select Run to view the results. Null dates appear as Missing, and populated dates appear in the specified format.
Show a replacement message in an Access form
Use an unbound text box to display a message without changing the stored date.
Work with date lists in WPS Spreadsheets
WPS Office is a free, lightweight alternative for working with spreadsheet date lists. Its familiar interface and Microsoft Office format compatibility make it easy to move supported documents between suites. It does not fix Access or replace Access database forms and queries.

Frequently Asked Questions
Can I put Missing directly in an Access Date/Time field?
No. A Date/Time field stores date and time values, not replacement text. Use a calculated query field or a form text box to display a message when the stored value is Null.
How do I show N/A instead of Missing?
Replace "Missing" with "N/A" in the IIf expression. For example, use IIf(IsNull([DateField]),"N/A",Format([DateField],"mm/dd/yy")) in a query.
Why do dates sort differently in the calculated display field?
The expression returns text, so Access sorts it as text rather than chronologically. Add the original Date/Time field to the query design grid and sort by that field instead.
Can users edit the date through the calculated form text box?
No. A text box with an IIf expression as its Control Source is display-only. Keep a separate text box bound to the original date field for editing.




