Read Right to first blank space

[Evan Robitaille]

I am trying to break out a field where city, state and zip codes have been consolidated. I want to create a field where I am able to ready the consolidated right either right or left until the first blank space. For example:

"pittsfield ma 01201"

I want to read the field to break out "pittsfield"

Thanks in advance for any help I can get on this

 

[The_Doc_Man]

Look at the InStr function in the Help Files. You need to work with VBA to do this best, but it is possible to nest some really ugly combinations of InStr and Right functions (see that in Help, too, if you don't like VBA) to split apart strings.

In summary, you use InStr to find a blank character, which can just be a blank in quote marks, starting from the left end of the object string. This finds the first blank from the left. You also need to know the length of the string, so look up Len function. This will tell you if the string was empty to start with.

OK, once you have the first blank, one of the InStr parameters lets you say, OK, starting from one character after that blank, find the next blank (if any). You can loop your way through the string, splitting off the blank parts at will. Since a loop is the best way to do this, that's why I suggested the use of VBA. Queries don't do very nice loops. Or very ugly ones, for that matter.

 

[mdemarte]

breaking out address: zip, then state, leaving city

Having just done this, I want to add to Doc Man's advice.

Think about San Francisco. If you break at the first space, you will not get what you want. Instead, start with the other end and break out the zip code first. I did successive query updates, pulling off the zip code and putting it into its own field and then removing zip code from the original field. Then, I worked with state. In your example, it is a two character abbreviation. Break that off the same way as zip code. That should leave you with the city.

Now, another piece of advice. Check your data first. I had some records without a zip code. I did a quick query to sort them in descending length order and checked them for missing zip codes. After I removed the zip codes with the query mentioned above, then I checked the state part.

Tedious, but effective! (Luckily, I had hundreds of records to check, not thousands.)

Last, I strongly suggest working with a copy of the database first before running update queries. Otherwise, it could be 'Messy, messy, messy!'

 

[The_Doc_Man]

Otherwise, it could be 'Messy, messy, messy!'

Truer words were never spoken.

Remember, addresses were created by MAN, so of COURSE there is no rhyme or reason as to why they look like they do.