Extract/convert date from string in MS Access

Question

I'm trying to extract date/times from strings with the following patterns and convert them to date types in Access.

"08-Apr-2012 21:26:49"
"...Confirmed by SMITH, MD, JOHN (123) on 4/2/2012 11:11:01 AM;"

Can anyone help?

Are those the only two patterns, or do you want to deal with arbitrary date formats? — Tikhon Jelvis, May 01 '12 at 00:06

Romeo · Answer 1 · 2012-05-01T00:56:19.640

1

Try this

    Dim d As Date
    d = CDate("08-Apr-2012 21:26:49")
    Debug.Print Format(d, "dd-MMM-yyyy")
    Debug.Print Format(d, "h:m:s")

Will give

08-Apr-2012
21:26:49

use this regex to get date-time between " on " (ie, space on space) and the ";" (first semi-colon after that).

(?<=\ on )(.*?)(?=\;)

edited May 01 '12 at 00:56

answered May 01 '12 at 00:09

Romeo

1,093
11
17

CDate works for the first pattern, but still not sure about how to handle the second. I first need to extract the date from the string. – regulus May 01 '12 at 00:41

score 1 · Answer 2 · edited May 23 '17 at 12:11

As already mentioned by Romeo in his answer, you need to use CDate() to convert a string with a valid date value to a Date variable.

You can get the date value out of the string like this:
(given that the strings always look like the one in the example, " on " (with blanks) before the date and ";" after it):

Public Function Test()

    Dim Source As String
    Dim Tmp As String
    Dim DateStart As Integer
    Dim DateEnd As Integer
    Dim DateValue As Date

    Source = "...Confirmed by SMITH, MD, JOHN (123) on 4/2/2012 11:11:01 AM;"

    'find the place in the source string where " on " ends
    DateStart = InStr(1, Source, " on ") + 4

    'find first semicolon after the date)
    DateEnd = InStr(DateStart, Source, ";")

    'get the part with the date
    Tmp = Mid(Source, DateStart, DateEnd - DateStart)

    'convert to date
    DateValue = CDate(Tmp)

End Function

score 0 · Accepted Answer · answered May 01 '12 at 12:16

Add this function to a VBA module:

' ----------------------------------------------------------------------'
' Return a Date object or Null if no date could be extracted            '
' ----------------------------------------------------------------------'
Public Function ExtractDate(value As Variant) As Variant
    If IsNull(value) Then
        ExtractDate = Null
        Exit Function
    End If

    ' Using a static, we avoid re-creating the same regex object for every call '
    Static regex As Object
    ' Initialise the Regex object '
    If regex Is Nothing Then
        Set regex = CreateObject("vbscript.regexp")
        With regex
            .Global = True
            .IgnoreCase = True
            .MultiLine = True
            .pattern = "(\d+\/\d+/\d+\s+\d+:\d+:\d+\s+\w+|\d+-\w+-\d+\s+\d+:\d+:\d+)"
        End With
    End If
    ' Test the value against the pattern '
    Dim matches As Object
    Set matches = regex.Execute(value)
    If matches.count > 0 Then
        ' Convert the match to a Date if we can '
        ExtractDate = CDate(matches(0).value)
    Else
        ' No match found, jsut return Null '
        ExtractDate = Null
    End If
End Function

And then use it like this, for instance in a query:

SELECT ID, LogData, ExtractDate(LogData) as LogDate
FROM   MyLog

Make sure you check that hte dates returned are in the proper format and make sense to you. CDate() interprets the date string in different ways depending on your locale.

If you're not getting the desired result, you will need to modify the code to separate the individual components of the date and rebuild them using DateSerial() for instance.

Extract/convert date from string in MS Access

3 Answers3

Linked