[AccessD] Search Memo field for number string

Here is a way to use regular expressions to scan the string.
I left it open ended so it will return any length number string
You may want to check for those shorter or longer
if you want it exact pattern can be changed to pick only length 7.
oops I just realized I missed case (a)  so the extraction pattern you 
really want is

re.pattern = 
"([0-9]{3}\.[0-9]{4})|([0-9]{3}\-[0-9]{4})|([0-9]{3}\#[0-9]{4})|([0-9]{3}\s[0-9]{4})"

here is a basic tutorial
http://www.zvon.org/other/PerlTutorial/Output/contents.html

Sub ScanStringForAnyNumbersunformated()
' DLL included with Windows Scripting Host
' this only applies to Access 97
'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

 Dim strToParse As String
 Dim objWordsFound As Object
 Dim singleWord As Variant
 'Set the early binding objects
    Dim re As New RegExp
        re.Global = True
        re.ignoreCase = True

    strToParse = "hello,To,you 8888888 999 9.77  9#888"
   
'Set WordsFound, using regular expressions to obtain the words
'separated by commas

re.Pattern = "[0-9]+\.[0-9]*)|([0-9]*\.[0-9]+)|([0-9]*\#[0-9]+)|([0-9]+)"

    Set objWordsFound = re.Execute(strToParse)
    For Each singleWord In objWordsFound
     Debug.Print singleWord
    Next
   
    strToParse = "hello To you"
   
'Set WordsFound, using regular expressions to obtain the words
'separated by blanks
    re.Pattern = "[^ ]+"
    Set objWordsFound = re.Execute(strToParse)
    For Each singleWord In objWordsFound
     Debug.Print singleWord
    Next
  End Sub

re.Pattern = "[0-9]+\.[0-9]*)|([0-9]*\.[0-9]+)|([0-9]*\#[0-9]+)|([0-9]+)"

explanation of search pattern,
I have code to do this.

any of
Followed by 0, -, 9 one or more times
Followed by .
Followed by any of 0, -, 9 zero or more times
Followed by or Pattern-1: (
    any of 0, -, 9 zero or more times
    Followed by .
    Followed by any of 0, -, 9 one or more times
)-end-Pattern-1
Followed by or Pattern-2: (
    any of 0, -, 9 zero or more times
    Followed by #
    Followed by any of 0, -, 9 one or more times
)-end-Pattern-2
Followed by or Pattern-3: (
    any of 0, -, 9 one or more times
)-end-Pattern-3

Robert L. Stewart wrote:

>Sheri,
>
>Actually, if you build each string you want to search for:
>
>"123 4567"  (a)
>"123.4567"  (b)
>"123-4567"  (c)
>"1234567"   (d)
>
>And use a "Like a or like b or like c or like d", you can find 
>exactly what you want.
>
>As for bolding, there is no way of doing that without using a 3rd 
>party tool like FMS's memo editing tool which would give you full RTF 
>type capability within the memo field.  And then it would be a lot of 
>coding to find the string and then bold it.
>
>Robert
>
>
>At 10:41 AM 10/31/2005, you wrote:
>  
>
>>Date: Mon, 31 Oct 2005 16:31:51 +0000
>>From: "Mark A Matte" <markamatte at hotmail.com>
>>Subject: Re: [AccessD] Search Memo field for number string
>>To: accessd at databaseadvisors.com
>>Message-ID: <BAY103-F5DD33C5CA5EE2C0AE606ED26C0 at phx.gbl>
>>Content-Type: text/plain; format=flowed
>>
>>Sheri,
>>
>>I can't help with the Bold part...but on the search...I'm guessing you are
>>looking at a phone number...or some other 2 part number sequence...either
>>way...you can seperate your number into two pieces of criteria ( 2 fields on
>>a form )...and the place something like:
>>Like "*" & [Forms]![frmTest1]![NumPart1] & "*" &
>>[Forms]![frmTest1]![NumPart2] & "*"
>>
>>in the MemoField Criteria or your query.  The problem with this...is that
>>the query doesn't care if there is 1 or 1000 characters between the
>>numbers...it still returns the records.
>>
>>Hope it gives some direction.
>>
>>Thanks,
>>
>>Mark A. Matte
>>    
>>
>>>From: "Sheri Hixson" <sdhi at kalamazoo.net>
>>>Reply-To: Access Developers discussion and problem
>>>solving<accessd at databaseadvisors.com>
>>>To: <accessD at databaseadvisors.com>
>>>Subject: [AccessD] Search Memo field for number string
>>>Date: Mon, 31 Oct 2005 10:36:08 -0500
>>>
>>>I need to search a memo field for any string of any numbers sequenced as
>>>follows,  ### #### or ###.#### or ###-#### or ####### . I also want to bold
>>>the
>>>number sequence found in each memo field. Anyone have any ideas on how to
>>>setup
>>>this query? Thanks for any help!
>>>
>>>
>>>
>>>Sheri
>>>      
>>>
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada