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 .
[CODE=vb]Private Sub YourTextBox_Bef oreUpdate(Cance l As Integer)
Dim I As Integer
Dim NumPart As String
NumPart = ""
For I = 1 To Len(Me.YourText Box)
If (Asc(Mid(Me.You rTextBox, I, 1)) > 47) And (Asc(Mid(Me.You rTextBox, I, 1)) < 58) Then
NumPart = NumPart & Mid(Me.YourText Box, I, 1)
End If
Next I

Me.ExtractedNum ber = NumPart
End Sub
[/CODE] 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.Extracte dNumber)

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?