Instr question

[Guest]

Is there a way to find the second occurence of a space " " with Instr()? For
example, I am using Trim(Left(MASTER.CSZ,InStr(MASTER.CSZ," ")-1)) to extract
a city name from a field that contains the city, state and zip. However, in
the case when a city name is two words, I need to go to extract to the second
space (I have filtered the records with two word city names).

Thanks in advance,

Bonnie

 

[Ken Snell \(MVP\)]

Yes:

Trim(Left(MASTER.CSZ,InStr(InStr(MASTER.CSZ," ")+1,MASTER.CSZ," ")-1))

 

[louisjohnphillips]

Yes:

Trim(Left(MASTER.CSZ,InStr(InStr(MASTER.CSZ," ")+1,MASTER.CSZ," ")-1))

--

Ken Snell
<MS ACCESS MVP>








- Show quoted text -

The instr function commonly takes the form of

nPos = instr( String1, String2).

This provides the character position where String2 is first found in
String1. There is an optional argument of StartPosition. Here

nPos = instr( StartPosition, String1, String2 )

finds the first occurrence of String2 within String1 but starting
after the StartPosition character.

If it is known that all Cities in the result set have two spaces, why
not try

sCity = mid( CSZ, 1, -1 + instr( instr( CSZ, " " ) + 1 , CSZ, " " ) )

The sCity starts with the first character of the CSZip.
It continues until reaching the character before the second space
character.

In this way, there is no need to apply the TRIM function.

 

[Guest]

Excellent, thanks. I remember at AT() and RAT() function in dBase that had a
parameter for the nth occurence. This answers the question.

Thanks.

 

[Guest]

Unfortunately they don't all have two spaces. Example:

New Rochelle NJ has two but Neward NJ only has one.

Thanks.

 

[Guest]

Unfortunatley they don't all have 2 spaces. Example:

New Rochelle NJ has two but Newark NJ only has one.

Thanks,

 

[Guest]

Your suggestion is perfect. With a CSZ of:

EL PASO NM 79932

Would I do: Instr(Instr(Instr(CSZ, " ")+1, CSZ, " ") to capture the state?

Thanks in advance.

Bonnie

 

[Guest]

Sorry, I spoke too soon (as usual).

Using your suggestions, here's how I parsed the city, state zips for either
EL PASO TX 79932 or CANTON GA 30144

To parse with only two spaces, first I ran:

UPDATE MASTER SET MASTER.CITY = Trim(Left(MASTER.CSZ,InStr(MASTER.CSZ,"
")-1)), MASTER.STATE = Trim(Mid(MASTER.CSZ,InStr(MASTER.CSZ," ")+1,2)),
MASTER.ZIP = Trim(Right(MASTER.CSZ,Len(MASTER.CSZ)-InStrRev(MASTER.CSZ," ")))
WHERE (((Len([CSZ])-Len(Replace([CSZ]," ","")))=2));

To parse with 3 spaces, I then ran:

UPDATE MASTER SET MASTER.CITY = Mid(MASTER.CSZ,1,-1+InStr(InStr(MASTER.CSZ,"
")+1,master.CSZ," ")), MASTER.STATE = Mid(MASTER.CSZ,InStrRev(Master.CSZ,"
")-2), MASTER.ZIP =
Trim(Right(MASTER.CSZ,Len(MASTER.CSZ)-InStrRev(MASTER.CSZ," ")))
WHERE (((Len([CSZ])-Len(Replace([CSZ]," ","")))>2));


Works like a dream and I thought it might help others on this community.

Thanks!