Splitting String using Instr and retrieving part information

[mous]

I know there are many posts on this but still cannot find what I want ....


I have a string ....

... <surname>bloggs</surname> <fornames>Jane</fornames> etc.etc.
... <surname>williams</surname> <forenames>Jo</fornames> etc.etc.

In a query I know how to:

Find the Start and End Postions as follows:

StartSurnamePos: InStr([string],"<surname>") returns 19
EndSurnamePos: InStr([string],"</surname>") returns 34

I also know that by adding 8 to StartSurnamePos I can get Bloggs but how do I stop it there.

Using left, Right or Mid how do I pull out just

bloggs Jane
Williams Jo in seperate fields?

Thanks in advanced.

 

[FoFa]

Can't you use an XML import function for this, it looks like XML.