从MS Access中的字符串中提取/转换日期

将此功能添加到VBA模块中:

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

确保您检查返回的日期格式正确，并且对您有意义. CDate()根据您的语言环境以不同的方式解释日期字符串.

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.

如果未获得理想的结果，则需要修改代码以分隔日期的各个部分，并使用DateSerial()进行重建.

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.