Instr() problem?

[Tcs]

I have records which contain, among others, 4 columns:

StrNmbr StrName MlgAddr MlgCity
--------- ---------- ---------------- ----------------
1234 WISTERIA 1234 WISTERIA DR WEST PALM BEACH
0 CLARK 3245 CLARK AVE MyCity

I need to fix all rows where StrNmbr = zero and MlgCity = "MyCity", and StrName
is contained in MlgAddr.

My stumbling point *seems* to be matching StrName to a portion of MlgAddr. I'm
trying:

Mid([MlgAddr],InStr(6,[MlgAddr]," ")+1) in the StrName column. It sort of
works, but I only get 3 matches, and I think I should have about many more.

From what I've seen, all street names in MlgAddr start in char pos 6.

Instead of trying to do all this in just one query, I've created several,
performing just ONE step at a time. (One query for matching the city, another
for the street number, etc.) But I was still getting too many. My query was
matching CLARK with WESTHEIMER. So I thought that perhaps I had too many
contraints in one query. Splitting them up hasn't helped. Changing my
contraint for the street name has, but now it's too much, asd I'm getting back
too little.

Can someone please shed some light on this problem for me?

Much appreciated, thanks in advance,

Tom

 

[Ken Snell [MVP]]

If you want to find the occurrence of a string in a field's value, do you
really need to care which character you start on?

Try this as the criterion (I assume you're working in VBA code?):

InStr([MlgAddr], strName) > 0


If you only want to test starting with the sixth character:

InStr(6, [MlgAddr], strName) > 0

 

[Tcs]

No, I wasn't working in VBA. I was trying to do it in a query. I've tried
several more permutations, but to no avail. I was hopeful that:

WHERE (((Mid$(6,[MlgAddr],Len([StrName])))=[StrName]));

would work, but now I get a "Datatype mismatch error", even though both fields
are text. (Length of StrName = 255, MlgAddr = 25.)

I guess I *have* to go with VBA. Which I *can* do, I was just trying not to...

 

[Ken Snell [MVP]]

A query cannot read a variable such as StrName (assuming that that is a
variable in VBA). You can use a function to get the value of a variable, but
it would have to be a global variable for the function to get its value.
--

Ken Snell
<MS ACCESS MVP>

No, I wasn't working in VBA. I was trying to do it in a query. I've
tried
several more permutations, but to no avail. I was hopeful that:

WHERE (((Mid$(6,[MlgAddr],Len([StrName])))=[StrName]));

would work, but now I get a "Datatype mismatch error", even though both
fields
are text. (Length of StrName = 255, MlgAddr = 25.)

I guess I *have* to go with VBA. Which I *can* do, I was just trying not
to...

If you want to find the occurrence of a string in a field's value, do you
really need to care which character you start on?

Try this as the criterion (I assume you're working in VBA code?):

InStr([MlgAddr], strName) > 0


If you only want to test starting with the sixth character:

InStr(6, [MlgAddr], strName) > 0

 

[John Spencer]

I'm not sure what you want to fix but I would try query something like the
following.

UPDATE TheTable
SET SomeField = SomeValue
WHERE StrNumber = 0
AND MlgCity = "MyCity"
AND MlgAddr LIKE "????? " & [TheTable].[StrName] & " *"

If the strName starts at variable places, but as long as there are some
characters before it you can change the match criteria to
Like "??* " & [TheTable].[StrName] & " *"

If waht you are trying to get is the Street Number then the SET clause of
the update query would probably look like:

SET StrNmbr = LEFT(1,MlgAddr,Instr(1,MlgAddr," ")-1)