Others

Display Missing or N/A for Blank Date Fields in Microsoft Access

Anum MushtaqAnum Mushtaq Nov 14, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Open the query in Design View

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.

2
Add a calculated 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.

3
Run the query

Select Run to view the results. Null dates appear as Missing, and populated dates appear in the specified format.

The calculated result is text: The display field is not a Date/Time value and is not directly editable. Keep the original date field in the query for date-based sorting, filtering, or editing where the query allows it.
Free Microsoft Office alternative

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.

Display a message for blank dates in a spreadsheet using a formulaOpen and work with Microsoft Excel XLSX filesUse a familiar spreadsheet interface for everyday data tasksAccess document, spreadsheet, and presentation tools in one suite
microsoft office alternative - wps office

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.