[AccessD] A2003: Extracting strings

MartyConnelly martyconnelly at shaw.ca
Fri Nov 18 11:26:35 CST 2005


It has been available since vbscript.dll came out. There were three 
version (5.0 or 1.0) 5.5 and 5.6
5.6 has a lot of additional methods to handle multi-line regexp, some 
may have been OS specific.

With Access 97 you had force a VBA reference in code with a specific 
entry points to old versions of the dll.

After 5.6 came out you could just do CreateObject("vbscript.regexp")
I think I cheesed off some Swedish programmer who was selling a mda 
solution for $50
when I posted the method on comp.databases.ms-access about 5 years ago.
.
I have a sample mdb at http://www.rogersaccesslibrary.com/OtherLibraries.asp
look under my name for RegularExpressionValidator. It is just code I was 
using for testing.


The most useful part is the regexp pattern documentor, I borrowed the 
VB6 code from Don Edwards Seattle.
in frmRegExTester. Handy for looking at long paragraph  length patterns. 
I have seen one for emails
that is 4 pages long.

"\d{1,2}[-]+\d{1,2}[-]+\d{1,4}"


digit 1 to 2 times
Followed by any of - one or more times
Followed by digit 1 to 2 times
Followed by any of - one or more times
Followed by digit 1 to 4 times


There is documentation to use RegExp under Script56.chm or Script55.chm
"C:\Program Files\Microsoft Windows Script\ScriptDocs\Script56.CHM"

Lots of sample patterns out there but most are for grep or pearl regexp
The syntax is a little different just remove the first and last 
character a ^ and $ I think.
There is a txt file with the sample mdb with old urls to regexp sites 
maybe out of date.

' DLL included with Windows Scripting Host or IE5 vbscript
'ReferenceFromFile "c:\windows\system\vbscript.dll\2
' Windows 2000: Requires a reference to VBScript Regular Expressions
' Microsoft VBScript Regular Expressions 1.0
' VBScript_RegExp - C:\WINNT\System32\vbscript.dll\2  Version  1.0
'                   C:\Windows\System\vbscript.dll\2
' Microsoft VBScript Regular Expressions 5.5
' VBScript_RegExp - C:\WINNT\System32\vbscript.dll\3  Version  5.5
'                   C:\Windows\System\vbscript.dll\3
' if using Version 5.5  Dim objRE As VBScript_RegExp_55.RegExp
'Dim objRE As VBScript_RegExp_10.RegExp
'Set objRE = New VBScript_RegExp_10.RegExp
"c:\Windows\System\vbscript.dll\3", True
 'ReferenceFromFile "c:\windows\system\vbscript.dll\4",true

Steve Erbach wrote:

>Marty,
>
>You know, I looked for the phrase "regular expression" in the "Help" but
>found nothing but the usual references to expressions: expression builder,
>expressions in queries, jet expression sandbox mode, etc. After getting your
>very helpful hint, I looked on my Safari Bookshelf in the "VBScript in a
>Nutshell" book, and voilà! There she is! RegExp object and all its methods
>and properties. Thank you.
>
>Has that object been available to me all this time? I mean, since Access 97
>or 2000?
>
>Steve Erbach
>Neenah, WI
>
>On 11/17/05, MartyConnelly <martyconnelly at shaw.ca> wrote:
>  
>
>>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.
>>>      
>>>

-- 
Marty Connelly
Victoria, B.C.
Canada






More information about the AccessD mailing list