[AccessD] A2003: Extracting strings

How about a Regular Expression function

'?getDateRegEx("aaaa 12-12-2005 888hh")
'?getDateRegEx("aaaa 13-13-04 888hh")
'?getDateRegEx("aaaa 12-12-04 888hh")

Public Function getDateRegEx(ByVal sValue As String) As Variant

'need reference to Microsoft VBScript Regular Expressions 5.5,
' or Set r = CreateObject("vbscript.regexp")
    Dim x As Integer
    Dim r As RegExp
    Dim buf As String
    Dim match As Object
    Dim matches As Object

    getDateRegEx = Null
    'throws a null into a  bad date like 13-13-2005

    Set r = New RegExp
    r.Pattern = "\d{1,2}[-]+\d{1,2}[-]+\d{1,4}"
   ' r.Pattern = "((?:19|20)\d\d)[- /.](0[1-9]|1[012])[- 
/.](0[1-9]|[12][0-9]|3[01][- /.](?:19|20)\d\d))"

    Set matches = r.Execute(sValue)
    For Each match In matches
        Debug.Print match.Value
        On Error Resume Next
        getDateRegEx = CDate(match.Value)
    Next match
    Set matches = Nothing
    Set r = Nothing
 End Function



Steve Erbach wrote:

>Dear Group,
> Lets say you have a note field in a table into which your users have been
>entering text notes that include dates in various formats (9-1-05, 10/12/04,
>8/3/2004, etc.) as well as the text. What I'd like to do is extract those
>dates from the note field and stuff them into a bona fide date field.
> It's straightforward enough to locate, say, all the dates that have dashes
>in them with a query like this:
> SELECT *
>FROM tblNotes
>WHERE ((Note Like "*##-##-##*")
>OR (Note Like "*##-#-##*")
>OR (Note Like "*#-##-##*")
>OR (Note Like "*#-#-##*")
>OR (Note Like "*##-##-####*")
>OR (Note Like "*##-#-####*")
>OR (Note) Like "*#-##-####*")
>OR (Note Like "*#-#-####*"));
>
>This gives me all the records that have dates formatted with dashes. But
>what I want is to actually extract those dates and stuff them into a new
>column as a Date type.
> I've looked through the Access 2003 Help for a while but I don't seem to
>find anything that jumps out at me. To use the Instr() function you need to
>supply a literal value, not a pattern. I guess what I'm looking for is a
>function similar to the Match() function in the old Borland Paradox program.
>You provided the string to search as well as the string pattern you're
>searching for and the Match() function would stuff the located substring
>into a variable for you.
> Anything like this that you can think of? I'm considering writing a VBA
>Match() function, but I'm hoping there's something else available. Thanks.
>
>Regards,
>
>Steve Erbach
>Scientific Marketing
>Neenah, WI
>www.swerbach.com <http://www.swerbach.com>
>Security Page: www.swerbach.com/security <http://www.swerbach.com/security>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada