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[OneorTwoDigitNumber]z
The field would look like this:
x123y3zx456y14zx543y7z 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.google.c om...

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[OneorTwoDigitNumber]z
The field would look like this:
x123y3zx456y14zx543y7z 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 OneorTwoDigitNumber as Integer
....
searchSTR = "x" & UserID & "y"
start = InStr(1, Forms!myFrmName.CheckList, searchSTR)

If start > 0 Then

OneorTwoDigitNumber = Val( Mid (Forms!myFrmName.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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQAROoIechKqOuFEgEQLmUQCgvb/C9zoMQXBk2SPty1pN0Qv/kdcAnRcC
7GGFJp/GmY3DMm60T4VxzqkF
=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[OneorTwoDigitNumber]z
The field would look like this:
x123y3zx456y14zx543y7z 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!myFrmName.CheckList,"y",1)

if iPos then

if mid(Forms!myFrmName.CheckList,ipos+2,1) = "z" or
mid(Forms!myFrmName.CheckList,ipos+3,1) = "z" then
'y??z found
zstart =
instr(Forms!myFrmName.CheckList,"z",instr(Forms!my FrmName.CheckList,"y",1))
zLength = instr(Forms!myFrmName.CheckList,"y",1),zStart

OneOrTwoDigitNumber = mid
(Forms!myFrmName.CheckList,zStart,zLength)
Endif