Extracting numbers from a txt field

[Karen]

I am trying to extract the first occurence of a number
(which is a year, 1995, 2000, etc) from a text field. The
first occurence of the year occurs after differing lengths
of text, for example, a simple one would be MB ChB 1941 NZ
or a more complex one: MRCS Eng LRCP Lond 1935; DCH 1937
RCP Lond; DLO 1937 RCP Lond RCS Eng; DPH 1938 Lond; MD
1939 Lond; FRACP 1964; MCCM (NZ) 1980; FAFPHM (RACP)
1994. I am only interested in pulling out the FIRST
occurrence of the field, i.e. 1941 and 1935 respectively.
Can anyone help? Could have used Val if the number came
first, but not here.

Thanks Karen

 

[Bob Tulk]

Assuming all the years begin with a 1 and no other 1's
appear in the field, enter:

Mid([Field1],InStr(1,[Field1],"1"),4)

in your query (the SQL would look like SELECT Mid
([Field1],InStr(1,[Field1],"1"),4) AS Expr1, InStr(1,
[Field1],"1") AS Expr2 FROM Table1