conver text to number

[sierralightfoot]

In access query, design view, create a new field/expression:
take any text value with numbers and convert to number value, ie:
123 123 becomes 123123
123-123 becomes 123123
1-2b-/m3123 becomes 123123

In other words, anything that is not a number is deleted and the balance is
numreric.

 

[John Spencer]

Are you converting the result to a number type or are you leaving it as a
string?

Here is a function that can be used to return a string

Public Function fStripToNumbersOnly(ByVal varText As Variant) As String
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As String
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = ""

Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[sierralightfoot]

Converting to a number.

Isn't there an easier expression I can use in design view by creatinga new
field?

 

[John Spencer]

If you are getting E followed by a number then I suspect that you are
seeing scientific notation for the results.


The "expression" is a vba function that you should copy and paste into a
vba module and save it with a name other than fStripToNumbersOnly. Then
in a query you can call the function

fStripToNumbersOnly([tablename].[FieldName])

IF there is a simpler way of doing this, I am not aware of it. The code
could be made more efficient and robust, but it works for me.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[sierralightfoot]

Here's something I used in access query for another database:

ExprA: Val(Replace(Replace([PARCLNUM]," ",""),"-",""))

It worked great and I was looking for something similar for my new
application.

 

[John Spencer]

Using nested replace calls should work fine for stripping out known
characters. The problem is you would have to call replace for each
character you wanted to strip out. If that were a limited number of
characters then use nested replace.

For your example of "1-2b-/m3123", you would need to nest 4 replace
statements. For 12/123.93mrCT5 you would need 6 replace calls
and to carry this out to an extreme 1Abcdefghijklmnopqrstuvwxyz_23.123, you
would need 28 nested replace calls.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Here's something I used in access query for another database:

ExprA: Val(Replace(Replace([PARCLNUM]," ",""),"-",""))

It worked great and I was looking for something similar for my new
application.

Are you converting the result to a number type or are you leaving it as a
string?

Here is a function that can be used to return a string

Public Function fStripToNumbersOnly(ByVal varText As Variant) As String
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As String
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = ""

Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..