Stripping everything but numbers in Access
keatonmill last edited by
Hi there. I have fields in access with data that looks like this:
I want to strip everything out that isn't a number, so I just have
Edit: By the way, I'm a long timer lurker, first time poster. Sorry that my first post is a silly question/demand!
Lingerance last edited by
Can you not make a string buffer and loop through your string(s) putting any numeric character into the buffer?
djork last edited by
I can't tell you how to strip them out in Access, but I just want to recommend that you may want to divide that 122-1771A field into two numbers in the end, 122, and 1771. You may have to sort by the first number or the second number and it could come back to bite you if they are concatenated.
dhromed last edited by
Strip them using what? SQL or VB?
RaspenJho last edited by
In access 2007 you could do use a custom function (in a module) to do the conversion, then call it from either SQL or VB. It is NOT going to be the fastest thing in the world, but it should do what you want...
Here is an example function: (Note that this does not support periods anywhere in the string)
Public Function ParseDbl(inp As String) As Double
Dim i As Integer
Dim result As String
Dim s As String
For i = 1 To Len(inp) Step 1
s = Mid(inp, i, 1)
If IsNumeric(s) Then
result = result & s
ParseDbl = CDbl(result)
SELECT ParseDbl(Field1) AS Expr1