InStr - to lookup exact match

[MartyO]

Hi there,
I have a field that is populated by a survey in Sharepoint where the user
can select mulitple answers relating to school grades. When I import the
data into my Access database it comes in like this "6; 7; 8; 9; 10; 11; 12"
or another user may have selected these "K; 1; 2; 3; 4; 5; 6; 7; 8" etc.
My code looks at the Field using the InStr function, if it finds say grade
"7" in the field, I write a record to another table using that grade.
The problem is with grades 10, 11 and 12 and grade 1. Using the InStr
function, when it searches for a 1, it will think it's found grade 1 when in
fact it's found grade 11.
Is there a way in the InStr function to make it find "exactly" what you are
searching for, grade 1?
Thanks in advance!
marty

 

[Dirk Goldgar]

Hi there,
I have a field that is populated by a survey in Sharepoint where the user
can select mulitple answers relating to school grades. When I import the
data into my Access database it comes in like this "6; 7; 8; 9; 10; 11;
12"
or another user may have selected these "K; 1; 2; 3; 4; 5; 6; 7; 8" etc.
My code looks at the Field using the InStr function, if it finds say grade
"7" in the field, I write a record to another table using that grade.
The problem is with grades 10, 11 and 12 and grade 1. Using the InStr
function, when it searches for a 1, it will think it's found grade 1 when
in
fact it's found grade 11.
Is there a way in the InStr function to make it find "exactly" what you
are
searching for, grade 1?

That's one of the difficulties of dealing with unnormalized data. You can
make this work by converting the input field to a more consistently
delimited version, and then searching for a fully delimited target. Like
this:

InStr(";" & Replace([YourField], " ", ";") & ";", ";" & [GradeSought] &
";")

Suppose that [YourField] = "6; 7; 8; 9; 10; 11; 12". Then the first
argument of InStr will evaluate to this:

";6;;7;;8;;9;;10;;11;;12;"

And if [GradeSought] is "1", the second argument of InStr will evaluate to
this:

";1;"

So it will not find a match. But if [GradeSought] is "11", the second
argument of InStr will evaluate to this:

";11;"

And that will find a match.

 

[Douglas J. Steele]

Add semicolons to the front and end, and include the semicolon in your InStr
call. Instead of:

InStr([YourField], [YourSearchTerm])

use

InStr("; " & [YourField] & ";", " " & [YourSearchTerm] & ";")

 

[MartyO]

I had tried that but couldn't get it just right. Thanks so much! It works
perfectly!

Marty

Add semicolons to the front and end, and include the semicolon in your InStr
call. Instead of:

InStr([YourField], [YourSearchTerm])

use

InStr("; " & [YourField] & ";", " " & [YourSearchTerm] & ";")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi there,
I have a field that is populated by a survey in Sharepoint where the user
can select mulitple answers relating to school grades. When I import the
data into my Access database it comes in like this "6; 7; 8; 9; 10; 11;
12"
or another user may have selected these "K; 1; 2; 3; 4; 5; 6; 7; 8" etc.
My code looks at the Field using the InStr function, if it finds say grade
"7" in the field, I write a record to another table using that grade.
The problem is with grades 10, 11 and 12 and grade 1. Using the InStr
function, when it searches for a 1, it will think it's found grade 1 when
in
fact it's found grade 11.
Is there a way in the InStr function to make it find "exactly" what you
are
searching for, grade 1?
Thanks in advance!
marty

 

[Harvey Thompson]

Hi there,
I have a field that is populated by a survey in Sharepoint where the
user can select mulitple answers relating to school grades. When I
import the data into my Access database it comes in like this "6; 7;
8; 9; 10; 11; 12" or another user may have selected these "K; 1; 2;
3; 4; 5; 6; 7; 8" etc.
My code looks at the Field using the InStr function, if it finds say
grade "7" in the field, I write a record to another table using that
grade.
The problem is with grades 10, 11 and 12 and grade 1. Using the InStr
function, when it searches for a 1, it will think it's found grade 1
when in fact it's found grade 11.
Is there a way in the InStr function to make it find "exactly" what
you are searching for, grade 1?
Thanks in advance!
marty

Marty,

Instead of InStr, use Split