Others

How to Convert YYYYMMDD Text to Real Dates in Microsoft Access

Aamir Naveed Akram Oct 9, 2026 868 views

Question details

The user needs to convert date information currently stored as text in YYYYMMDD format into true date values so they can be properly used in queries and date-range filters.

How to Convert YYYYMMDD Text to Real Dates in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Handling supplier data containing date fields (like FailureDate, CorrectDate, and ClaimDate) that are stored as text, which prevents standard date operations in queries.
Observed behavior: The date fields are recognized by the system as plain text instead of date formats, making it impossible to apply date ranges and chronological criteria.

Before you start

Ensure you understand the exact field names in your table that contain the text dates, and verify that all records follow the strict 8-character YYYYMMDD format before running the conversion query.

Solution 1Recommended

Use DateSerial and String Functions in a Query

Create a calculated field in your Access query to parse the text and convert it into a true date value.

The YYYYMMDD text format combines the year, month, and day into a single continuous string. Microsoft Access provides the DateSerial function, which can be combined with Left, Mid, and Right string-extraction functions to separate these components and reassemble them into a valid, functioning date.

Open Query Design

Open your Access database, navigate to the Create tab on the ribbon, and click on Query Design.

Add your table

Select and add the table containing your supplier data and the specific date text fields you wish to convert.

Input the conversion formula

In an empty Field column in the query design grid, type the expression: RealFailureDate: DateSerial(Left([FailureDate],4),Mid([FailureDate],5,2),Right([FailureDate],2)).

Run the query

Click the Run button (red exclamation mark) on the Query Design tab to view the results. The new column will display true date values.

Use DateSerial and String Functions in a Query

Apply to Other Fields: You can apply this exact same formula structure to convert other fields. Simply replace [FailureDate] with [CorrectDate] or [ClaimDate] in your expression.

Solution 2

Convert Numeric Text Strings to Numbers

If you need to extract actual numeric values from text fields rather than dates, use the Val() function.

Free Microsoft Office alternative

Manage and Convert Data Easily with WPS Office

While Microsoft Access is used for complex databases, many data management tasks and text-to-date conversions can be effortlessly handled in WPS Spreadsheet. Enjoy a comprehensive, lightweight office suite that meets your daily data needs without the steep learning curve.

Easily convert text strings to dates using built-in formulas identical to standard spreadsheet software.Fully compatible with Microsoft Office formats, including Excel (.xlsx, .xls) and CSV data files.Lightweight and free alternative, ensuring fast loading times on any operating system.Familiar user interface makes data management simple with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just change the field data type to Date/Time in Access?

Changing the data type directly in the table design might result in data loss or parsing errors. Access often fails to recognize the continuous YYYYMMDD text string as a date. It is much safer to use a query with the DateSerial function to accurately interpret the custom format.

How do I update my original table with these new date values?

First, add a new field to your table and set its Data Type to Date/Time. Next, create an Update Query, add the table, select your new date field, and place the DateSerial formula into the 'Update To' row. Running this query will permanently populate the new field with real date values.

What happens if a field is blank or contains an invalid string?

If the text field is empty or contains fewer than 8 characters, the Left, Mid, and Right functions will return errors when passed to DateSerial. You can prevent this by adding an IIf() statement to check the string length before attempting the conversion, or by filtering out blank records in the query criteria.

Office Quick Solutions