Geting a number out of a string

[Ian D]

I am new to access and I would like some help

I have a field called reference and contains the some of
the following information

Inv 0125
789
Cr 456
cancel 748

If wish just to extract the numbers so above becomes

0125
789
456
748

Hope you can help

 

[Newbie]

Open a module and copy the following function into it

Public Function GETnum(somedata As String)
TempStr = ""

MyString = somedata
For i = 1 To (Len(MyString))
If IsNumeric(Mid(MyString, i, 1)) Then
TempStr = TempStr & (Mid(MyString, i, 1))
End If

Next
GETnum = TempStr

End Function

You can then use this in your queries like so

SELECT Table1.somedat, GETnum([somedat]) AS Numfield
FROM Table1;

HTH