Split a Field

[GetReel]

Help

I have a field calledd address field that contains the street name and suburb. Is there any way to remove the suburbs and add them to a field called suburnbs in the same table.

When i first did the db I failed separate the address details so that I can do report by suburbs.

 

[spacepro]

Do a search, this as been covered many times.

for example:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=56807&highlight=split+value+in+field

Andy

 

[GetReel]

thanks for that. I would have done a search but I didnt what key works to use.

I tried using the following :
Right([Address],Len([Address])-InStr([Address]," "))

Obviously Im doing something wrong because the entired address updated to the Suburb field in the update query except for the street number.

I wanted it to remove the suburb in the address field and add it to Suburb field.

currently my values in the address field are entered like this.
14/45 Hall Street, Fairfield