CODEWITHSUNDEEP
regexms-accessvba
regulus
regulus

Reputation: 969

Extract/convert date from string in MS Access

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

  1. "08-Apr-2012 21:26:49"

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

Can anyone help?

Upvotes: 2

Views: 3740

Answers (3)

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

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.

Upvotes: 0

Christian Specht
Christian Specht

Reputation: 36431

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

Upvotes: 1

Romeo
Romeo

Reputation: 1093

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 )(.*?)(?=\;)

Upvotes: 1

Related Questions