Instr() problem?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tcs - 14 Nov 2005 18:55 GMT

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

Reply to this Message

Ken Snell [MVP] - 14 Nov 2005 18:59 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

>I have records which contain, among others, 4 columns:
>
[quoted text clipped - 33 lines]
>
> Tom

Reply to this Message

Tcs - 14 Nov 2005 20:24 GMT

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?
[quoted text clipped - 6 lines]
>
>InStr(6, [MlgAddr], strName) > 0

Reply to this Message

Ken Snell [MVP] - 14 Nov 2005 20:42 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> No, I wasn't working in VBA. I was trying to do it in a query. I've
> tried
[quoted text clipped - 19 lines]
>>
>>InStr(6, [MlgAddr], strName) > 0

Reply to this Message

John Spencer - 14 Nov 2005 21:12 GMT

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)

>I have records which contain, among others, 4 columns:
>
[quoted text clipped - 33 lines]
>
> Tom

Reply to this Message