Dividing an address into house number and street name

[hibbie0762]

I am looking for VBA code which will scroll through about 60,000 records dividing an address line field into two new fields, one containing the house number and the other the street name, using the first space from the left which it finds as the separator (for example datAddress "13 Elm Street" could convert to datNo "13" and datStreet "Elm Street&quot.

I am using Access 97. Any help which you can offer would be gratefully accepted. One thought which did occur is that I might be better trying this in MS Excel and then porting the data back across?

 

[BoxHead]

Hibbie,

Combining the Instr, Mid and Left Functions should get you what you want. You might want to look at the Trim function as well.

datNo = Left(datAddress,Instr(datAddress, " ")-1)

datStreet = Mid(datAddress, Instr(datAddress, " ")+1)

HTH
John

Use what you have,
Learn what you can,
Create what you need.