Separating house number from street address

[MattRK]

I have big database with customer information. There is one address field that contains the house number and the street address. (1212 main st.) Is there a way to seperate the house number (1212) and the street address (main st.) and put them into seperate fields (house_number and street_address). I know i can cut and paste, but i dont really have time to do it 500 times. lol. Is there a built in tool for this? Mabey a macro? I dont know. Thanks for any help and advice you can provide.

-Matt Keller

 

[jimturner]

As far as I know there is no magic tool for this. You'll have to write your own. Since you're a programmer it should be pretty straight forward.

 

[paisana]

Data Junction @

http://www.datajunction.com

 

[mikey69]

I usually do this osrt of thing by moving the table to an Excel spreadsheet and then separating at spaces into separate columns and then combining all the bits of the street name

 

[RiverGuy]

If you don't have any half's, like 325 1/2 Main St., Then you could break at the first space in the string, and everything left of it for the number, everything right of it to the street.

 

[MattRK]

Well, i ended up just cuting and pasteing. lol. But if i wanted to redo it all, how would i break at the first space in the string? I only have like 2 1/2's. I can just do those by hand. Thanks!!

-Matt Keller

 

[tviman]

Riverguy had the right idea but rather than worrying about spaces, you would break at the first alpha character There's always a better way...

 

[MattRK]

Yes, but what about apartments? lol 106B S. Something st.. It would break 106 away from B S. Something st. Oh well.

-Mr.K

 

[AlexMidd]

To find the first space, use the Instr function. Have fun! )

Alex Middleton

 

[RiverGuy]

I guess there is no one solution for this. If I was you, I would fix whatever you have, and then for the future, make a street number and street field separate.

 

[mikey69]

River Guy's last post says it all. Whatever you do, the next one will be different. I've just had one thrown at me with phone numbers, which need to be imported from accounting software, Some have a leading zero. This really messes things up, believe me! And there are thousands of them. There ain't nothing but to get in and sort them out, step by step

 

[tviman]

What's your reason for wanting to seperate the house number number from the street, apartment, and/or suite nbr? Off the top of my feeble little brain, I can't think of any good reason to split them now, or at time of data entry. Sooner or later, you're going to have to put them back together. What gives? There's always a better way...

 

[MattRK]

Well i need to order the records first by thier street address and then by thier house number. so instead of
1 a street
2 c street
3 c street
4 b street

it would be:

1 a street
4 b street
2 c street
3 c street

-Mr.K

 

[mikey69]

Database design theory suggests that you should separate number from street name and future flexibility encourages you to do so, for precisely the reasons that you give , MattRK. go for it!

 

[TomCologne]

Reply to mikey69, Aug19:

Have you tried
"Right([yourPhoneNoFld],10)/ Left([yourPhoneNoFld],1)"
in an UpdateQry? (American 10 digit)
I have to update an Access Address db from an Excel list regularly. That list has some numbers with leading zeros which I eliminate with the qry.
To make it more interesting, it contains a ZIP code field, where a Zero is used as a placeholder instead of a dash, in most cases that is. Sometimes no Zero is used at all or it is put at the end, resulting in a column like this:

ZIPcode
1111102222
111112222
1111122220
1111100000
111110000

But the right UpdateQry at least reduces manual editing to 5% of all addresses.
Maybe this will help you erase that "1".

You're not alone

TomCologne

 

[mikey69]

Reply to Tomcologne - thanks for your help. I do in fact have a similar technique. One of the hurdles is that it's not easy to tell Excel precisely what sort of field it should pass to Access, and this is further complicated if the Excel doc is a text file import. There are always ways around. It simply depends whether the effort is justified.