Access2K: Extract a string

[Lauren Quantrell]

This a a long convoluted string parsing question...
I have a string in an Access 2K database table field that I use for
noting if a user has checked a record.
The string goes like this:
x[UserID]y
So the field looks like this:
x123yx456yx543y and so on...

I run a LIKE query when the form loads to determine the state of
control CheckBox indicating if the user has checked the record:

If Forms!myFrmName .CheckList Like "*x" & UserID & "y*" Then
Forms!myFrmName .CheckBox = True
Else
Forms!myFrmName .CheckBox = False
End If

I run a string of code to remove the x[UserID]y string from field
Checklist if the current user unchecks the control CheckBox.

So far so good.

The system works fine, but now I want to include another variable, so
that the
string would go like this:
x[UserID]y[OneorTwoDigitNu mber]z
The field would look like this:
x123y3zx456y14z x543y7z and so on...

So here's the question...
I can still search for Forms!myFrmName .CheckList Like "*x" & UserID &
"y*" to check or uncheck the CheckBox control.
But how do I extract:
Forms!myFrmName .CheckList Like "*x" & UserID & "y" & ?? & "z*"
I will know "x123y" but how can I determine what follows "y" before
"z"?

(I know this could be done by creating a table creating a new record
linking users and records in the old table, but I want to stick with
this system for this particular use...)

Any help is appreciated...
lq

[Roberto Spier]

"Lauren Quantrell" <la************ *@hotmail.com> escreveu na mensagem
news:47******** *************** ***@posting.goo gle.com...

This a a long convoluted string parsing question...
I have a string in an Access 2K database table field that I use for
noting if a user has checked a record.
The string goes like this:
x[UserID]y
So the field looks like this:
x123yx456yx543y and so on...

I run a LIKE query when the form loads to determine the state of
control CheckBox indicating if the user has checked the record:

If Forms!myFrmName .CheckList Like "*x" & UserID & "y*" Then
Forms!myFrmName .CheckBox = True
Else
Forms!myFrmName .CheckBox = False
End If

I run a string of code to remove the x[UserID]y string from field
Checklist if the current user unchecks the control CheckBox.

So far so good.

The system works fine, but now I want to include another variable, so
that the
string would go like this:
x[UserID]y[OneorTwoDigitNu mber]z
The field would look like this:
x123y3zx456y14z x543y7z and so on...

So here's the question...
I can still search for Forms!myFrmName .CheckList Like "*x" & UserID &
"y*" to check or uncheck the CheckBox control.
But how do I extract:
Forms!myFrmName .CheckList Like "*x" & UserID & "y" & ?? & "z*"
I will know "x123y" but how can I determine what follows "y" before
"z"?

(I know this could be done by creating a table creating a new record
linking users and records in the old table, but I want to stick with
this system for this particular use...)

Any help is appreciated...
lq

Hi,

Dim searchSTR as String
Dim start as Integer
Dim OneorTwoDigitNu mber as Integer
....
searchSTR = "x" & UserID & "y"
start = InStr(1, Forms!myFrmName .CheckList, searchSTR)

If start > 0 Then

OneorTwoDigitNu mber = Val( Mid (Forms!myFrmNam e.CheckList, start +
Len(searchSTR) ) )

Forms!myFrmName .CheckBox = True
Else
Forms!myFrmName .CheckBox = False
End If

HTH
Roberto

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You stated the solution in your question. Use the wildcard character
"?" (question-mark) to indicate the 2 unknown characters between y and
z. E.g.:

If Forms!myFrmName .CheckList Like "*x" & UserID & "y??z*" Then
HTH,

MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAROoIechKq OuFEgEQLmUQCgvb/C9zoMQXBk2SPty1 pN0Qv/kdcAnRcC
7GGFJp/GmY3DMm60T4Vxzq kF
=H6My
-----END PGP SIGNATURE-----
Lauren Quantrell wrote:

This a a long convoluted string parsing question...
I have a string in an Access 2K database table field that I use for
noting if a user has checked a record.
The string goes like this:
x[UserID]y
So the field looks like this:
x123yx456yx543y and so on...

I run a LIKE query when the form loads to determine the state of
control CheckBox indicating if the user has checked the record:

If Forms!myFrmName .CheckList Like "*x" & UserID & "y*" Then
Forms!myFrmName .CheckBox = True
Else
Forms!myFrmName .CheckBox = False
End If

I run a string of code to remove the x[UserID]y string from field
Checklist if the current user unchecks the control CheckBox.

So far so good.

The system works fine, but now I want to include another variable, so
that the
string would go like this:
x[UserID]y[OneorTwoDigitNu mber]z
The field would look like this:
x123y3zx456y14z x543y7z and so on...

So here's the question...
I can still search for Forms!myFrmName .CheckList Like "*x" & UserID &
"y*" to check or uncheck the CheckBox control.
But how do I extract:
Forms!myFrmName .CheckList Like "*x" & UserID & "y" & ?? & "z*"
I will know "x123y" but how can I determine what follows "y" before
"z"?

(I know this could be done by creating a table creating a new record
linking users and records in the old table, but I want to stick with
this system for this particular use...)

Any help is appreciated...
lq

[Tym]

On 13 Jan 2004 10:19:59 -0800, la************* @hotmail.com (Lauren
Quantrell) wrote:

If Forms!myFrmName .CheckList Like "*x" & UserID & "y*" Then
Forms!myFrmName .CheckBox = True
Else
Forms!myFrmName .CheckBox = False
End If

Something like this???

Dim iPos as Integer
Dim zStart as Integer
dim zLength as Integer

iPos = 0

iPos = instr(Forms!myF rmName.CheckLis t,"y",1)

if iPos then

if mid(Forms!myFrm Name.CheckList, ipos+2,1) = "z" or
mid(Forms!myFrm Name.CheckList, ipos+3,1) = "z" then
'y??z found
zstart =
instr(Forms!myF rmName.CheckLis t,"z",instr(For ms!myFrmName.Ch eckList,"y",1))
zLength = instr(Forms!myF rmName.CheckLis t,"y",1),zSta rt

OneOrTwoDigitNu mber = mid
(Forms!myFrmNam e.CheckList,zSt art,zLength)
Endif