Split first two words of string into new field

[Sharkiness]

Morning All,

I cannot find the answer to my question anywhere.

I have an address field named 'ADDRESS'. I want to run an update query so that the first two words of the address are split into a new field named 'AddressSplit'.

Therefore if the full address showed the following

'Ruby Cottage Edinburgh Scotland'

I would get 'Ruby Cottage' in new field.

Likewise if the address was '5 Aberdeen Way Glasgow Scotland'

I would get '5 Aberdeen' in the new field.

Would be grateful of any help and would prefer a query sequence rather than VBA code.

Thanks

Sharkiness

 

[vbaInet]

You don't need to save it into a new field. It's bad practice to do so. Just run the code anytime you want the first two words of the address field:

left([AddressField], instr(instr(1, [AddressField], " ") + 1, [AddressField], " ") - 1)

 

[Sharkiness]

Thanks Mate,

Works a treat

 

[vbaInet]

No problemo!

 

[angellio]

Hi vbaInet,
I would like to split category separate with >
Example:
women > apparel > something else
I need to do this in ACCESS 2007
Thank you in advance