Question Locked

How can I split a text field such as St. Address

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am dealing with specific way to work on an a text field with a "street address"

(if P.O or PO) next record else split the 'stNumber' from the 'stName'.

I have used samples from microsoft and others.
Help!!!!

5 replies

by thumbsup2

Does the language you're working with have a feature you can search through the contents of the data one character at a time and look for the first space, then peel the data before the space off to another field?

If you told us what you're working with, it might help.

by ctsguy

The program is MS Acess 2003. The table is a comma dilimited. I have imported it to an Accss Table.

by larryd4

Well if its already a table in Access then its not comma delimited.

Since your already in MS-Access your gonna need to do this with VBA. Is that an option?

If not it might be smart to open the originl comma delimited in excel and seperate the data their. Then import it in to Access.

by thumbsup2

Use a For/Next loop limited by the length of the field and look at each character in turn. Checking each character one by one, if the character you're looking at is not a blank, concatenate it into one holder variable. When the for/next loop reaches a blank, escape and compare the contents of that variable to what you're looking for (i.e. =P.O.,P.O,PO,P O). If it's a match, next record and do it again. If not, BINGO, you've nailed your street address.

Of course, that assumes that everyone either puts in PO or a street number first! ;)

This discussion is closed to new replies.