Others

Format a Date of Birth as DD/MM/YYYY in a Microsoft Access Query

Huda QurayshiHuda Qurayshi Nov 14, 2026 868 views

Question details

Display a stored date of birth in DD/MM/YYYY format in a Microsoft Access query without changing the underlying date value.

Format a Date of Birth as DD/MM/YYYY in a Microsoft Access Query
Product
Microsoft Access
Device & OS
not provided
Scenario
A user wants query results to show dates of birth in day/month/year order.
Observed behavior
The query should display each date of birth in the requested format while leaving the table's stored date value unchanged.
Before you start

Identify the table and date-of-birth field used by your query, and check that the field contains Date/Time values. Replace the example table and field names with your own.

Solution 1Recommended

Use the Format Function in an Access Query

Format the query result without changing the date stored in the table.

1
Open SQL View

In Access, open an existing query in Design View and select View > SQL View, or create a query using Create > Query Design and switch to SQL View.

2
Add the Format expression

Enter SELECT Format([TableName].[DateOfBirth], "dd/mm/yyyy") AS [Date of Birth] FROM [TableName]; in the SQL editor. Replace TableName and DateOfBirth with your actual names.

3
Run the query

Select Run on the query toolbar to display the formatted Date of Birth column.

Use the Format Function in an Access Query
Display and sorting: Format returns text, so sort by the original date field if you need chronological order. The slash in the format can follow the Windows regional date separator.
Free Microsoft Office alternative

Work With Exported Access Dates in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with Office files. Export Access query results to a spreadsheet to continue working with dates in WPS; WPS does not fix Access queries or replace Access database functionality.

Format exported dates in WPS SpreadsheetsOpen and save Microsoft Excel XLSX files for easier migrationUse a familiar spreadsheet interfaceWork with a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Does the Format function change the date stored in my Access table?

No. Format changes the query result into formatted text; it does not modify the underlying Date/Time value in the table.

Why does the query show a different date separator?

The slash in an Access Format pattern represents the date separator specified by Windows regional settings. Check the Windows regional date settings if you need slashes.

How can I sort the results chronologically?

In Query Design, add the original DateOfBirth field and set its Sort row to Ascending or Descending. The formatted result is text, so sorting it may not produce chronological order.

Can I use this expression in Query Design instead of SQL View?

Yes. In an empty Field cell in the query design grid, enter Date of Birth: Format([DateOfBirth], "dd/mm/yyyy"), using your actual field name, then select Run.