Others

How to Fix Microsoft Access Sorting Formatted Dates in the Wrong Order

WPS Content Manager Sep 29, 2026 868 views

Question details

Users need to sort dates correctly in Microsoft Access after the Format() function causes them to sort alphabetically instead of chronologically.

How to Fix Microsoft Access Sorting Formatted Dates in the Wrong Order

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating database queries or reports that group daily totals and require chronological sorting across different months and years.
Observed behavior: When applying the Format() function (e.g., mm-dd-yyyy) in a query, dates are converted to text, causing Access to sort them in alphabetical order rather than by actual date.

Before you start

Verify that your original field is stored as a Date/Time data type in the Access table design, rather than Short Text, to ensure date logic can be correctly applied.

Solution 1Recommended

Sort Queries by the Underlying Date Value Instead of Formatted Text

To achieve proper chronological sorting, group and order your database query using the raw date field or DateValue function, and reserve the Format() function strictly for display purposes.

When the Format() function is used in an Access query, it transforms the Date/Time value into a text string. Consequently, Access evaluates the characters sequentially. For instance, a formatted text date of '01-02-2026' will be sorted before '12-01-2025' purely because the character '0' precedes '1', ruining the chronological sequence.

The most effective way to resolve this is to separate the data logic from the presentation layer within your SQL statement.

Open Query Design View

Open your Microsoft Access database, navigate to your queries, right-click the query displaying the incorrect sort order, and select 'Design View' or 'SQL View'.

Modify the Sorting Logic

Remove the sorting criteria from the column where you applied the Format() function. Instead, apply the sort to the original Date/Time field.

Implement the DateValue Function

If you need to group by daily totals without time data, use the expression DateValue([YourDateField]) in your GROUP BY and ORDER BY clauses.

Update SQL Syntax

In SQL view, ensure your statement ends with an ORDER BY clause targeting the raw date. For example: ORDER BY DateValue([WorkDate]) DESC;

Retain Format() for Display Only

Keep your formatted field (e.g., Format([WorkDate], 'mm-dd-yyyy')) in the SELECT statement as an aliased column solely so the end-user sees the preferred layout in the query results.

Sort Queries by the Underlying Date Value Instead of Formatted Text

Best Practice: A better alternative for reports and forms is to omit the Format() function in the query entirely and instead set the 'Format' property of the text box control in the Form/Report Design view.

Free Microsoft Office alternative

Looking for a Free, Lightweight Office Suite to Analyze Data?

While WPS Office does not include a direct database management equivalent to Microsoft Access, it offers a powerful, free alternative for Word, Excel, and PowerPoint. If you frequently export datasets for analysis, WPS Spreadsheet provides robust, intuitive sorting, filtering, and pivot table features to manage your date fields effortlessly without needing to write SQL queries.

1. Download and Install: Visit the official WPS Office website to download and install the free suite on your device.
2. Export Access Data: From Microsoft Access, export your table or query results as an Excel Workbook (.xlsx) or CSV file.
3. Analyze in WPS Spreadsheet: Open the exported file in WPS Spreadsheet, select your date column, and use the 'Sort & Filter' button to easily arrange dates from oldest to newest.

Seamlessly compatible with Microsoft Office formats including XLSX, DOCX, and PPTX.Easily sort, format, and analyze dates in WPS Spreadsheet with built-in chronological sorting tools.Lightweight software package with a familiar, tabbed user interface for instant productivity.Completely free to use with integrated PDF editing and conversion tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why do dates from earlier years sometimes appear after recent years in Access?

This happens when your dates are stored or queried as text (often caused by the Format() function). In text-based sorting, Access compares characters from left to right. A string starting with '01' (January) will always sort before '12' (December), regardless of the actual year included at the end of the text.

What is the difference between DateValue() and Format() in MS Access?

DateValue() converts a string or a date/time stamp into a pure Date serial number, which Access natively understands how to calculate and sort chronologically. Format() takes a value and turns it into a specifically styled text string, which is great for visual presentation but terrible for data sorting.

Can I format dates properly without changing my SQL query?

Yes. Instead of using the Format() function within your SQL query, leave the field as a standard Date/Time type in the query. Then, open the Form or Report that uses this query, open the Property Sheet for the specific date text box, and set its 'Format' property to your desired layout (e.g., mm-dd-yyyy).

Office Quick Solutions