[AccessD] Regular Expressions?

You could do simply
Debug.Print StrConv("24CM 10MM ADAPTOR SLEEVE LG", vbProperCase)
which doesn't  quite cut it. It returns

24cm 10mm Adaptor Sleeve Lg

At that point you may have to use RegEx replace to change these 
idiosyncracies
such as

'?replacetest("mm ") ' mm + space
Function ReplaceTest(replStr)
  Dim regEx, str1                   ' Create variables.
  str1 = "24CM 10MM ADAPTOR SLEEVE LG"
  Set regEx = CreateObject("VBScript.RegExp")         ' Create regular 
expression.
  regEx.Global = True               ' Set global applicability.
  regEx.Pattern = "MM "            ' Set pattern.
  ReplaceTest = regEx.Replace(str1, replStr)   ' Make replacement.
End Function


 The VBScript RegExp object provides 3 properties and 3 methods to the 
user.

Properties 
  Pattern   
  IgnoreCase   
  Global   

Pattern - A string that is used to define the regular expression. This 
must be set before
  use of the regular expression object. 
IgnoreCase - A Boolean property that indicates if the regular expression 
should be tested
  against all possible matches in a string. By default, IgnoreCase is 
set to False.
Global - A read-only Boolean property that indicates if the regular 
expression
  should be tested against all possible matches in a string. By default, 
Global is set to False.

Methods 
  Test (search-string) 
  Replace (search-string, replace-string) 
  Execute (search-string) 

Test (string) - The Test method takes a string as its argument and 
returns True if
 the regular expression can successfully be matched against the string,
 otherwise False is returned.

Replace (search-string, replace-string) - The Replace method takes 2 
strings as its arguments.
 If it is able to successfully match the regular expression in the 
search-string,
 then it replaces that match with the replace-string, and the new string 
is returned.
 If no matches were found, then the original search-string is returned.

Execute (search-string) - The Execute method works like Replace, except 
that it returns
 a Matches collection object, containing a Match object for each 
successful match.
 It doesn't modify the original string.

Regular Expressions tend to be faster than InStr or Like searches over 
large strings
by at least a factor of 2. A help file for Regular Expressions can be 
found in
JScript5.chm and VbScrip5.chm, these are contained in the Windows 
Scripting Host 2.0


Jim DeMarco wrote:

>Joe,
>
>Not sure what you'd be using RegEx for?  AFAIK they won't format your
>data just find items that match a pattern.
>
>Jim DeMarco
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe Rojas
>Sent: Wednesday, March 15, 2006 12:56 PM
>To: Access Developers discussion and problem solving
>Subject: [AccessD] Regular Expressions?
>
>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
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada