split street number and street address

[gloria]

Non programmer asks for syntax help:

typical addresses are 220 Broadway, 151 E. 42nd Street, 21
Bertha Drive, etc.

I need address to split in fields which I'll call
streetnumber and streetname.

Suggestions for appropriate update language in query are
requested which I can copy and paste into an update field.

 

[JulieD]

Hi Gloria

this should work
StreetNumber: Left([address],InStr([address]," ")-1)
StreetAddress: Right([address],Len([address])-InStr([address]," "))

Where [address] is the name of your current field

here's the sql view of an update query using the above
UPDATE Table3 AS Table3_1 SET Table3_1.streetnumber =
Left([address],InStr([address]," ")-1), Table3_1.streetname =
Right([address],Len([address])-InStr([address]," "))
WHERE (((Table3_1.address) Is Not Null));


Regards
JuielD

 

[Guest]

Thanks, JulieD! I had exactly the same question; I'm glad I checked here!

Rich

 

[JulieD]

Hi Rich

thanks for the feedback ... this is a fantastic resource isn't it!

Cheers
JulieD