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

Hi Darren

Another method is to use Split to create arrays to look up the keys and values, indeed if you need "a little more".
Here's an example to look up values from connect strings which are not always perfectly formatted.

</code>
Public Function GetConnectKey( _
  ByVal strConnect As String, _
  ByVal strKey) _
  As String

' Extracts the value of key strKey or of the type from a string
' containing a type and one or more sets of key/value separated
' by semicolon, like:
'
'   "TYPE;KEY1=VALUE1;KEY2=;;KEY3=VALUE3;
'
' as typically found in the Connect field of table MSysObjects for
' an attached non-ODBC table;
' or without a type value, like:
'
'   "KEY1=VALUE1;KEY2=;;KEY3=VALUE3;
'
' as typically found in the Connect field of table MSysObjects for
' an attached ODBC table.
'
' Returns value of type if key strKey has a length of zero.
' Returns empty string if key/value set is malformed.
'
' 2004-05-28. Cactus Data ApS. CPH.
  
  ' Separators.
  Const cstrSepConnect  As String = ";"
  Const cstrSepEntry    As String = "="
  
  Dim astrEntries() As String
  Dim astrEntry()   As String
  
  Dim strEntry      As String
  Dim strProfile    As String
  Dim lngElements   As Long
  Dim lngN          As Long
  
  ' No special error handling.
  On Error Resume Next
  
  ' Create array with entries.
  astrEntries = Split(strConnect, cstrSepConnect)
  ' Loop to locate entry with key.
  For lngN = LBound(astrEntries) To UBound(astrEntries)
    strEntry = Trim(astrEntries(lngN))
    If Len(strEntry) = 0 Then
      ' Empty entry.
    Else
      ' Create array holding Key and Value.
      astrEntry = Split(strEntry, cstrSepEntry)
      lngElements = UBound(astrEntry)
      ' A valid entry will have one or two elements.
      If lngElements < 0 Then
        ' Empty ("") entry.
      ElseIf lngElements > 1 Then
        ' Malformed ("..=..=..") multi-element entry.
      ElseIf Len(astrEntry(0)) = 0 Then
        ' Malformed ("=" or "=VALUE") entry.
      Else
        ' Key ("KEY=VALUE") or type ("TYPE") is present.
        If Len(strKey) = 0 Then
          ' We are looking for the type.
          If lngN = 0 And lngElements = 0 Then
            ' Type is located. Retrieve its value.
            strProfile = astrEntry(0)
          End If
        ElseIf StrComp(astrEntry(0), strKey, vbTextCompare) = 0 Then
          ' Key is located.
          If lngElements = 1 Then
            ' Key has a value. Retrieve this.
            strProfile = astrEntry(1)
          End If
        End If
      End If
    End If
    If Len(strProfile) > 0 Or Len(strKey) = 0 Then
      ' Either the key has been located or
      ' this is a lookup for the type.
      Exit For
    End If
  Next
  
  GetConnectKey = strProfile
  
  End Function
</code>

>>> darren at activebilling.com.au 27-01-2010 03:31 >>>
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