Re: [MSAccessProfessionals] Stripping out a zipcode

Hi jfakes,

You can try use a combination of Right(), InstrRev(), and Len().

RIght(JobAddressLine3, Len(JobAddressLine3)-InstrRev(JobAddressLine3," "))

Regards,

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of jfakes.rm via groups.io <jfakes=rocketmail.com@groups.io>
Sent: Friday, August 21, 2020 11:30 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] Stripping out a zipcode

 

My office uses a commercial software package that is the worst designed SQL server in the world.

I'm making a report to group by several fields, right now the zipcode.  I need to extract the zipcode out of the field [JobAddressLine3].  The problem is, the company threw in the city, state, and zip into one field for example:  AURORA CO                80013
There isn't a set number of spaces between the state and the zipcode.  Now I can use a simple code like:  ZipCode: Right([JobAddressLine3],5).  This works fine, except, when the address contains a zip +6 (80111-540577) then my code returns 40577 instead of the 80111.  There are a ton of other variables that I can't control such as punctuation (Denver, CO vs Denver CO for example).

Any ideas on how to strip the zip out taking into account the zip + 6 issue?

I've attached a spreadsheet with a few examples, I've changed the addresses to 5555 for privacy issues.