How to Extract Dates from Text Strings in Microsoft® Access Databases

Using Access VBA to extract a date from unstructured strings of text that are a muddle of dates and other text.

Last updated on 2024-05-15.

Preface

Many of the databases I've been engaged to update share the same issue: users have been forced to input a mix of information into a single field because sufficient fields have not been provided.

In migrating the data to a properly structured database, the text-string content of that single field needs to be broken down and distributed across a number of new fields.

Commonly, a date needs to be isolated from the text string. The date's location in the text string, and even its format, makes extracting it a challenge. This is an example of the mix of values I use in creating a formula for isolating:

In the data I've had to process, the dates within the text string, thank goodness, have been input consistently in one format, e.g. dd/mm/yyyy or mm/dd/yyyy.

At the moment I'm dodging any attempt at making one function fit all formats.

Strategy

I'm proposing these conditions for deciding that a text string does contain a date:

1. There is a character sequence of ?#/?#/??## (# is a digit, ? is a digit or is not present)
2. The supposed date is bounded by spaces. You might consider that this condition limits the effectivenes of the function. Agreed, up to a point. However, I'm wary having seen sequences of digits and / characters representing product codes, for example.
3. From a string that appears to contain more than one date, the first occurence is picked
4. For the time being, a sequence of characters construed as a date, is a date in the order day/month/year (UK/Danish format)

There is text that might have been intended to include a date, but which our function will not treat as such:

1. A character seqence such as 5/9. It's too risky assuming 5/9 is a date. Hence character sequences having one / only are not treated as dates.
2. Our function ignores a ?#/?#/??## sequence suggesting a date that can't possibly be an actual date — for example 29/02/2019.

The Date Extraction Function

This is my first attempt at the VBA date extraction function:

Here's the result of a select query that has as its record source a table containing the text strings from which we want to extract dates:

The second column shows the function at work. It returns a number that represents the date according to its position in Access's built-in calendar. This calendar assumes the numeric value of one for January 1 1899 and inclements by a count of one for each day since then.

The third colum is the same as the second in the use of the function but additionally is formatted to display the date number in a recognisable form.

A format has a number of parts, the first three relate to positive numbers, negative numbers and zeros. To separate the parts you use a semi-colon. An example of applying a three-part format appears below.

Attending to the Zeros

The function returns a zero when it doesn't detect a date within the text string or when the string it's supplied with for some reason causes it to throw an error. You could suppress the 30/12/1899 in the third column by modifying the format property to read dd/mm/yyyy;;"".

But this is a cosmetic effect only. What if you want the zero values to be ignored altogether? One way to achieve this is to wrap the result of the dmwGetDate function in a conditional statement:

IIf(dmwGetDate([Text String])=0,Null,dmwGetDate([Note]))

With the dd/mm/yyyy field format property applied, the data extraction is completed in a single column:

Further Work

I intend to provide variations of the function to make it suitable for use with ISO (yyyy-mm-dd) and US (mm/dd/yyyy) date formats.

Initial testing indicates the that function unmodified seems to cope with ISO dates well enough.

It doesn't work with US ones, but I'm confident that this is to do with the regional settings of the computer on which I'm working:

How to Work with Dates and Times in Microsoft Access Databases is a complete overview of working with dates in Access.

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.