Rearranging Text in Text Field

[pjordan@drcog.org]

Ok so I have a text field called Locatio. The format is something like this:

ARAPAHOE RD & 18300 E
CALEY CIR & 9663 E


etc.

What I want to do is find a way to take all the text including the & and place it at the very front so that the results are:

& 18300 E ARAPAHOE RD
& 9663 E CALEY CIR

Which after that I can do a search/replace and remove the & to have a valid address.

Is there an easy way to do this in a query?

Thanks

[ranman256]

mid([Locatio],instr([Locatio],"&")) & " " & left([Locatio],instr([Locatio],"&")-1)

[lfpm062010]

SELECT Mid([Locatio],InStr(1,[Locatio],"&")) & " " & TRIM(Mid([Locatio],1,InStr(1,[Locatio],"&") - 1))
FROM TABLENAME;

[pjordan@drcog.org]

Thanks guys