Getting string values between colons

W

Wylie C

I have table field formatted as text. The form control is bound to this
field. The user will input stock number code with colons as the separator for
different aspects of the product code. For example sss:yyy:rrr. Each of the
values (sss or yyy or rrr) may be 1,2 o3 three characters in length. What I
need to do is assign strVal1 to whatever value is input between the start of
the field and the first :, assign strVal2 to the value between the 1st and
2nd :, then strVal3 to the value that follows the last :.... Ideally I would
like to have a function that would loop until the last value, that without a
trailing : is found. Can someone please help me?

Thank you all very much.
 
D

Dan Artuso

Hi,
If these values need to be seperate, that's the way they should be entered
and stored. It's *much* easier to concatenate values for display purposes
than it is to parse them!

Having said that, here is a routine to do it:

Public Sub ParseColon(strIn)
Dim strVal1 As String
Dim strVal2 As String
Dim strVal3 As String

strVal1 = Left(strIn, InStr(1, strIn, ":", vbTextCompare) - 1)
MsgBox strVal1

strVal2 = Mid(strIn, InStr(1, strIn, ":", vbTextCompare) + 1)
strVal2 = Left(strVal2, InStr(1, strVal2, ":", vbTextCompare) - 1)
MsgBox strVal2

strVal3 = Mid(strIn, InStr(1, strIn, ":", vbTextCompare) + 1)
strVal3 = Mid(strVal3, InStr(1, strVal3, ":", vbTextCompare) + 1)
MsgBox strVal3
End Sub

You can incorporate the parsing code anyway you like. The above sub was just for testing.
 
S

Sprinks

Hi, Wylie.

See VBA Help on the following functions:

InStr() Finds the position of a substring within another string
Left() Returns a number of characters from the left of as string
Mid() Returns a substring from another string

So the strategy would be find the first colon, and assign the substring to
the left of it to your variable, then strip off the substring and the colon
from your working string, and repeat until InStr returns 0, meaning it didn't
find a colon. Your last variable is equal then to the working string.

Hope that helps.
Sprinks
 
D

Douglas J. Steele

Assuming you're using Access 2000 or newer, try using the Split function:

Dim strInput As String
Dim varArray As Variant

strInput = "sss;yy;rrrr"
varArray = Split(strInput, ";")
strVal1 = varArray(0)
strVal2 = varArray(1)
strVal3 = varArray(2)
 
S

Sprinks

Thanks, Doug, for making me aware of this new function. Sure makes parsing a
lot easier!

Sprinks
 
D

Douglas J. Steele

My problem is that I'm still doing most of my work in Access 97, so I always
have to import a module to simulate the new VBA functions.
 
Top