how do i split an address field into two fields ?

[harsha]

i have imported an excel file into access and i have around 3000 address. i
need to split the address field into two fileds by using the space in between
the streeet number and the street name.
for example:
all my records look like this: (122 Smith st)--- one field
i would like to split it into (122) --1st field and (Smith st) -- 2nd field

 

[Brendan Reynolds]

If all of your addresses really do look like that (number followed by space
followed by street) then you simply need to take everything to the left of
the first space as the number, and everything to the right of the first
space as the street ...

UPDATE Table1 SET Table1.[Number] = Left$([Address],InStr(1,[Address],"
")-1), Table1.Street = Mid$([Address],InStr(1,[Address]," ")+1);

Of course, if any of your addresses deviate from that pattern - and if you
have 3000 of them it will be very surprising if none of them do - then
things can become a lot more complicated.

But at least the above example should do *most* of the work for you.