Specific selection from a record 1

[michaelacheson]

Hello,

I have records like the ones below stored in FieldA:

UK 9 591663 998 7
UK 9 4221 1677 6

I want to select the bold section between the space after 9 and the next space. Eg. from these 2 examples I want to select 591663 and 4221 and put them in FieldB. The records will always start with UK 9 but the next section could vary from 1 number to a maximum of 6. After the bold section that I want selected, there will always be a space followed by a random amount of numbers.

Many Thanks,
Michael

 

[scriverb]

Here is SQL to parse the field as you requested.
SELECT tblYourTableName.FieldA, Mid$([FieldA],InStr(1,[FieldA],"9&quot+2,(InStr(InStr(1,[FieldA],"9&quot+2,[FieldA]," &quot)-(InStr(1,[FieldA],"9&quot+2)) AS FieldB
FROM tblYourTableName;

You just have to change the bolded table name to the name of your table.
Bob Scriver

 

[michaelacheson]

Thanks that worked a treat.

Michael Acheson

 

[scriverb]

Great. Glad to be of assistance. Thanks for the star. Bob Scriver