How do I parse an address field to separate street # from street n

[Guest]

I need to do this to each record in the table, saving the Street# and
StreetName in two fields of each record. From here, I can sort by
StreetName/Table

 

[Guest]

Assuming that there is a space between the street number and the street name
for each record then you could update the two new fields with the following
just place this in an Access Query SQL View, replace the table and field
names with your own:

UPDATE Table1
SET Table1.StreetNum =
Left([Table1]![StreetAddress],InStr([Table1]![StreetAddress],Chr(32))-1),
Table1.StreetAddr =
Trim(Right([Table1]![StreetAddress],Len([Table1]![StreetAddress])-InStr([Table1]![StreetAddress],Chr(32))));

Take Care & God Bless ~ SPARKER ~