Extracting the Numbers from a field in a query

[RobH]

Hi

I have fields in a database that include numbers eg:

"Slimline Tank - Fibre Plastic : 1,125 l. WHEAT"
I need to effectively extract the 1125 from the above and place it in another Field

And before anyone asks no its not always after the : or in the same possition.
Some Entries dont have a size at all.

[missinglinq]

Where the original data is in YourTextBox and the resulting extracted number is going into a textbox named ExtractedNumber.

Private Sub YourTextBox_BeforeUpdate(Cancel As Integer)
Dim I As Integer
Dim NumPart As String
NumPart = ""
For I = 1 To Len(Me.YourTextBox)
 If (Asc(Mid(Me.YourTextBox, I, 1)) > 47) And (Asc(Mid(Me.YourTextBox, I, 1)) < 58) Then
   NumPart = NumPart & Mid(Me.YourTextBox, I, 1)
 End If
Next I
 
Me.ExtractedNumber = NumPart
End Sub
 

Notice that the "number" you get is actually a string. If you need to use for for calculations you can convert it to a real number with

Val(Me.ExtractedNumber)

Welcom to TheScrips, Rob!

Linq ;0)>

[RobH]

If i needed to do this as part of a post import process via a query any suggestions?