Geting a number out of a string

  • Thread starter Thread starter Ian D
  • Start date Start date
I

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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top