Separating a Date from surrounding text

Question

I'm working with a Microsoft Access database. In the database, there is a remark field where some data is stored along with a date. I'm trying to extract both the month and the year from this field but I'm having a bit of difficulty.

There is no uniformity in how the dates and remarks are stored. For example, in some column, it will be "REMARK MM/DD/YYYY" while in other columns it may be "MM/DD/YYYY REMARK." The comments are of varying length themselves. This field is stored as a character field.

We now need to work with the months and years (not the date itself) being entered into this column and I'm looking for a way to easily extract this data and put it into two separate columns in the same table, as this data will be compared to a string entered by the user. (i.e., it will generate a query with all data from a particular month and year depending on variables given by the user, so let's say it will find all people that were marked as absent in January 2014 - this part is working for everything except finding people in a specific month and year as it currently returns all absent users entered over the course of time).

I've already made the columns to store the data and simply called them 'Month' and 'Year'. I unfortunately did not design the database (it was built about 10 years ago) and I only have a very basic knowledge of SQL. I'm also using Access 2002, because unfortunately I don't have access to a newer version. Any help you can give me on this topic would be greatly appreciated.

you probably want this: http://stackoverflow.com/questions/1411072/parse-a-date-from-unformatted-text-in-sql — DLeh, Jan 28 '15 at 16:31
My though is you could key in on the date using index of the first `/` but isn't it also possible that `/` is an actual character in the remarks? — SQLChao, Jan 28 '15 at 16:51

score 1 · Answer 1 · answered Jan 29 '15 at 11:28

You could use a custom VBA function that takes advantage of regular expressions to find a string of characters matching ...

one or two decimal digits
a slash
one or two more decimal digits
a slash
four more decimal digits

... separate those numeric values and return a date value, something like this:

Option Compare Database
Option Explicit

Public Function ExtractDateFromText(TextToSearch As String) As Variant
    Dim rgx As Object  ' RegExp
    Dim rgxMatches As Object  ' MatchCollection
    Dim rgxMatch As Object  ' Match
    Dim yyyy As Integer, mm As Integer, dd As Integer
    Dim rtn As Variant

    Set rgx = CreateObject("VBScript.RegExp")  ' New RegExp
    rgx.Pattern = "(\d{1,2})/(\d{1,2})/(\d{4})"
    Set rgxMatches = rgx.Execute(TextToSearch)
    If rgxMatches.Count = 0 Then
        rtn = Null
    Else
        Set rgxMatch = rgxMatches(0)
        mm = Val(rgxMatch.SubMatches(0))
        dd = Val(rgxMatch.SubMatches(1))
        yyyy = Val(rgxMatch.SubMatches(2))
        rtn = DateSerial(yyyy, mm, dd)
    End If

    Set rgxMatch = Nothing
    Set rgxMatches = Nothing
    Set rgx = Nothing

    ExtractDateFromText = rtn
End Function

Then you could update a Date/Time field in your table with the extracted date values like so

UPDATE Table1 SET Table1.DateFromRemark = ExtractDateFromText([remark]);

and then use the Year() and Month() functions to get the values you need.

Separating a Date from surrounding text

1 Answers1