Format a Date of Birth as DD/MM/YYYY in a Microsoft Access Query
Question details
Display a stored date of birth in DD/MM/YYYY format in a Microsoft Access query without changing the underlying date value.

- 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.
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.
Use the Format Function in an Access Query
Format the query result without changing the date stored in the table.
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.
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.
Select Run on the query toolbar to display the formatted Date of Birth column.

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.

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.




