Help stripping a zipcode

[Guest]

I want to strip a 5 digit ZipCode from a string.

The string contains the City, State and either a 5 or 7 digit ZipCode.

Chicago IL 12345-7890
Chicago IL 12345

So the thought would be to identify the first blank space from the right and
then extract the 5 characters to the right of the blank.

Can someone help me get started?

Thanks

Dave

 

[Guest]

Try

Mid([FieldName],InStrRev( [FieldName]," ")+1,5)

 

[John Spencer]

look up help on InStrRev (2000 SP2 and later) which can be used to find
the first space starting from the right. Another method would be to use
Instr and search for the dash and combine that with the MID function


Mid([CityStateZip],Instr(1,[CityStateZip] & "-","-") -6,5)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Thank you!!

That did it

Dave

Try

Mid([FieldName],InStrRev( [FieldName]," ")+1,5)

--
Good Luck
BS"D

I want to strip a 5 digit ZipCode from a string.

The string contains the City, State and either a 5 or 7 digit ZipCode.

Chicago IL 12345-7890
Chicago IL 12345

So the thought would be to identify the first blank space from the right and
then extract the 5 characters to the right of the blank.

Can someone help me get started?

Thanks

Dave

 

[John W. Vinson]

I want to strip a 5 digit ZipCode from a string.

The string contains the City, State and either a 5 or 7 digit ZipCode.

Chicago IL 12345-7890
Chicago IL 12345

So the thought would be to identify the first blank space from the right and
then extract the 5 characters to the right of the blank.

Well, that falls down when you get to San Francisco or Salt Lake City.

You can (in A2002 or later) use the InStrRev function - In String Reverse:

Left(Mid(InStrRev([CityStateZip], " ")+1, 5)

InStrRev will find the position of the *last* blank in the field; Mid() will
extract whatever is to the right of that (e.g. 12345 or 12345-4122), and
Left() will get just the five digits.

John W. Vinson [MVP]