Extracting Dates from Unstructured Text Strings in Microsoft Access Databases
A custom Access VBA function for isolating a date from a text string in which the date is mixed with other text in no particular order.
Last updated on 2019-10-29 by David Wallis.
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 information 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:
- TPB/SG 14/05/2017
- 29/04/2017 AH
- SG/DP phone Jill first
- 13/04/2017 Bob Smith
- Bob Smith
- | Mae Greene
- RB toDp
- BK to PCAT
- Steve Dicks/SG
- GG +4(0)632900843
- Andy Pink & Gina Blue
- Pete 5/9
- TPB/SG 4/5/2017
- TPB/SG 4/5/17
In the data I’ve had to process, the dates within the text string have been input consistently in one format, thank goodness! At the moment I’m dodging any attempt at making one function fit all.
I'm proposing these conditions for deciding that a text string does contain a date:
- There is a character sequence of ?#/?#/??## (# is a digit, ? is a digit or is not present)
- 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.
- From a string that appears to contain more than one date, the first occurence is picked
- 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:
- 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.
- 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 seperate 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:
With the dd/mm/yyyy field format property applied, the data extraction is completed in a single column:
I intend to provide variations of the function to make it suitable for use with US dates (mm/dd/yyyy) and ISO (yyyy-mm-dd) formats. Initial testing indicates the that function unmodified seems to cope with ISO dates well enough, but goes squiffy with US ones.
Your Support for dmw TIPS
If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —
Thanks, in anticipation.