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

Drew Wutka DWUTKA at Marlow.com
Wed Jan 27 18:37:40 CST 2010

LOL, well when you get the chance, try my method, it's a pretty simple
problem and gives a little insight on how a class works.

I wish back when I first started getting into VB/VBA, that I knew what I
know now about classes and collections.  I had a project when I first
started working for my current employer that took me 6 to 8 months to
get the first version out, and over the next few years I kept adding
functionality and tweaks.  About 2 years ago, I decided to rebuild the
system from scratch, and this time I built it with classes and
collections doing a majority of the work.  Had the existing capabilities
of the old system and then some, in about 2 weeks.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren -
Active Billing
Sent: Wednesday, January 27, 2010 4:48 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] A2003:Replacing 'tokens' in a string

Team - thanks heaps for the assistance and suggestions
But - OMG!!! You blokes assume waaaaaaay too much
You are actually assuming I know what you are talking about
Classes and collections?????? I have no clue
I have managed to get a Procedure to do what I wanted with prompting
the crew - but I am a luddite - Way over my Head all these suggestions 

Thanks heaps for all the effort 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Thursday, 28 January 2010 4:32 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A2003:Replacing 'tokens' in a string

Darren, I see you have plenty of help on this so far, but I just wanted
to show you a slightly more elegant and versatile solution using classes
and collections.

First, we create a class to represent a 'token', which has a name and a
value.  (For simplicities sake, we'll just make all values a string for

Option Explicit
Public TokenName As String
Public TokenValue As String
Property Let TokenString(strEnter As String)
Dim strArray() As String
strArray = Split(strEnter, "=")
TokenName = Mid(strArray(0), 2, Len(strArray(0)) - 2)
TokenValue = strArray(1)
End Property

Save the above as a class module named Token.

Now, personally, I would then create a 'collection class', but we can do
without that for right now, and just create a collection, and a 'filler

So behind a form, we need to declare a collection at the module level:

Option Explicit
Dim Tokens As Collection
Function GetTokens(strText As String)
Dim tk As Token
Dim strTokens() As String
Dim i As Long
Dim strData As String
'Ok, first drop the first and last double quote, don't need them
strData = Mid(strText, 2, Len(strText) - 2)
'Now let's break the tokens out:
strTokens = Split(strData, Chr(34) & "," & Chr(34))
'Setup the collection
Set Tokens = New Collection
'and now let's put the tokens into the collection:
For i = 0 To UBound(strTokens)
    Set tk = New Token
    tk.TokenString = strTokens(i)
    Tokens.Add tk, tk.TokenName
    Set tk = Nothing
Next i
End Function

Above, the GetTokens class builds the Tokens collection, so that when we
are done, we have a collection of tokens, keyed off of their names, and
holding their values.  So to use the token class and 'filler function'
we would put the following behind a command button (for testing):

Private Sub Command1_Click()
Dim strText As String
'a variable version of the string you submitted as your sample
strText =
'Run the filler function to split the string up
GetTokens strText
'we are good to go, now any token can be retrieved by its token name
MsgBox Tokens("InvoiceNo").TokenValue
MsgBox Tokens("AccountNo").TokenValue
End Sub


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren -
Active Billing
Sent: Tuesday, January 26, 2010 8:32 PM
To: AccessD
Subject: [AccessD] A2003:Replacing 'tokens' in a string

Hi team

Assuming the string below


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
constant - Otherwise I'd just use MID()

Nor will the length of the invoice number - Some may be 4 digits others

I need to pull just the Invoice Number (And the Account Number) out of
string to build a new string being used elsewhere

Many thanks in advance

The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.

More information about the AccessD mailing list