[AccessD] A2003:Replacing 'tokens' in a string

Darren, here is another method to extract your information, works in
Access '03.  Paste this in a module and step through using F8 one line at
a time and placing the mouse cursor over the variable to watch the values.

Function testRegexp() As Variant
   Dim myValue As Variant
   Dim pattern As String
   Dim regexMatch As Boolean
   Dim count As Integer
   Dim objvalue As Variant

   pattern =
"[AccountNo]=1234,[InvoiceNo]=1234567,[InvoiceDate]=04/01/2010,[Name]=Barry
"

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

   regex.Global = True
   regex.ignorecase = True

   ' the [] has a special meaning regex so make them literal
   ' by adding a \ preceding the []'s
   ' note the 2 sets of ()'s
   regex.pattern = "(\[InvoiceNo\]=(\d+))"

   ' or you can use the one below
   'regex.pattern = "(InvoiceNo]=(\d+))"

   regexMatch = regex.test(pattern)

   If (regexMatch) Then
      Set matches = regex.Execute(pattern)
      count = matches.count

      ' loop thru objects in matches
      For Each Object In matches
         ' this gets you the outer ()
         objvalue = Object.Value

         ' this gets you the inner ()
         myValue = matches(0).submatches(1)

      Next
   End If

End Function

Mike...


> Hi team
>
> Assuming the string below
>
> "[AccountNo]=1234","[InvoiceNo]=1234567","[InvoiceDate]=04/01/2010","[Name]=
> Barry"
>
> How would I get just the Invoice Number bit = Eg 1234567?
>
> The 'token' [InvoiceNo] will be constant followed by an "=" sign
>
> But sadly the position of the [InvoiceNo] token in the string will not be
> constant - Otherwise I'd just use MID()
>
> Nor will the length of the invoice number - Some may be 4 digits others 6
> etc
>
> I need to pull just the Invoice Number (And the Account Number) out of the
> string to build a new string being used elsewhere
>
> Many thanks in advance
>
> Darren
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>