[AccessD] Regular Expressions?

Joe, here is a modified function that uses regular expression I created in 
Access 2000.  I removed my specific stuff and added a couple possible 
patterns for you.  You will need to finish the rest but this should get 
you going.  I would strongly suggest using the debugger to step through 
the lines to confirm it is working.  Set the following variables in the 
debugger:
regexMatch
matches(0).submatches(0)
matches(0).submatches(1)
myValue
desc
pn

Change rst to match your query or table and change .Fields("zzz") at the 
beginning of while loop.

'
'
'
Function myRegex() As Boolean
    Dim regex As Object
    Dim rst As DAO.Recordset
    Dim desc As String, pn As String, myValue As String
    Dim regexMatch As Boolean
    Dim matches As Variant

    Set rst = CurrentDb.OpenRecordset("qryYourQry")

    Set regex = CreateObject("VBScript.RegExp")
    regexMatch = True

    regex.Global = True
    regex.ignorecase = True

    With rst
       .MoveFirst
       While (Not .EOF)
          desc = .Fields("Description")
          pn = .Fields("PartNumber")


          ' one of these patterns should work for to capture
          ' at start of line digit-char space digit-char
          regex.pattern = "(^(\d+[a-z]+\s+\d+[a-z]+))"
          regex.pattern = "(^([0-9a-z ]+))"
          regexMatch = regex.test(pn)

          If (regexMatch) Then
             ' matche(s) from part number
             Set matches = regex.Execute(pn)

             ' get last match
             myValue = matches(0).submatches(1)

             ' do your work on the captured value

             ' save your changes in another field
             .Edit
             .Fields("Value") = myValue
             .Update
          End If

          .MoveNext
       Wend
       .Close
    End With

    Set regex = Nothing
    Set rst = Nothing

End Function


Goodluck, Mike...


On Wed, 15 Mar 2006, Joe Rojas wrote:

> How can I add the ability to use regular expression in Access 2000?
>
> **Background**
> We are performing data conversion from our old MRP system to our new ERP
system and we are using Access to massage the data in between the two
systems.
> One of the decisions that we made was to go from all capital letters in
our part number descriptions to proper case. At first I thought I would
just convert each word to proper case but then I noticed that some of our
descriptions look like "24CM 10MM ADAPTOR SLEEVE LG" and we would like the
resulting text to look like "24CM 10MM Adaptor Sleeve LG" not "24cm Adaptor
Sleeve Lg".
> My best guess is to use regular expressions to identify most patterns and
then probably go over the data manual to make sure we didn't miss anything.
>
> Anyone have a better idea?
>
> Thanks,
> Joe Rojas
> IT Manager
> TNCO, Inc.
> 781-447-6661 x7506
> jrojas at tnco-inc.com
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>