Breaking up text into pieces 1

[boiker]

I have a field with addresses. i want to be able to sort by street name. from what i know, i need to create two more data records one for the number part. and one for the streetname part

CURRENTLY:

Address
--------
300 NE Jefferson St.
400 S Elm St.
1213 Oak Ave.

-some addresses have two letter directions, some one, some no direction.

I need to be able to split the addressess so that i get:

Address Number Dir Street
300 NE Jefferson St. 300 NE Jefferson St
400 S Elm St. 400 S Elm St
1213 Oak Ave 1213 Oak Ave

any ideas?

 

[dannyocean]

Use instr command and mid command. Try this

in a new field in a query 1stblank:Instr("",[Address}) this will return the location of the first blank. Then in another field stnum:Left([Address], [1stblank]-1) this should return the 1st string. Then you repeat and use mid instead of left. I ahve used this to split names, so it should work fine just will take a little experimentation.

HTH

DO

 

[boiker]

ok.. i don't know how this function works or where to type it. would you elaborate?

 

[UncleJack]

The suggested routine probably won't work because it depends on the number and position of spaces in the string. Since both will vary the generalized solution will only work in certain cases.

I think I would use a for-next loop testing the values of the characters as you move through the line. The function would ignore any character with an ASCII value that indicates it's a number. Then it would look at the next character to see if it matches a defined list of direction indicators such as N. or NE, etc. After ignoring direction indicators take the next string of character up to the end of the string or the next space as the street name.

Or you could start from the end of the string and move forward to the first space. Test the characters at the end of the string against a list such as St. Street ST Ave Dr Drive, etc. and ignore these if you find a match. Proceed then to the next appearing space and take the characters between the spaces as the street name. Sounds a lot more complicated than it really is.

Basic structure would be:

For x = Len(MyString) to 1 step -1
If Mid$(MyString, x, 1) = " " then
{Test for street indicators}
End if
{Continue moving backwards in the string looking for
the next space}

Next x

Have fun with this one.

Uncle Jack