Getting only Numbers from a Field

[ERF]

I have a field that contains some numbers and others with alphanumeric numbers. I am trying to extract the numbers so it only shows numbers.


Example:
192.5 (A)(B)
23512 (A)(B)(1)
459 (A)
490.5 E (A)
74.83544

To Show:
192.5
23512
459
490.5
74.83544
I am a novice to the Access program and I am trying several different criteria with no luck. I assume that I need to build an expression, but I am having a difficult time doing that as well. Any help would be appreciated. If you could also explain it for me so I can learn as I go, it would be great!

Thanks in advance!!!

[ssanfu]

You didn't provide any info about table name or field name, but given the sample data and the results wanted, try the following.

Create a new standard module (or use an existing standard module) and paste in the following function:

Function Get_Val(pTextField As String) As Double

    If InStr(pTextField, " ") = 0 Then
        Get_Val = pTextField
    Else

        Get_Val = Left(pTextField, InStr(pTextField, " ") - 1)
    End If
End Function

Create a new query.
Switch to SQL view and paste in the following SQL:

SELECT YourTableName.Field1, Get_Val([field1]) AS Expr1
FROM YourTableName;

Change "YourTableName" to your table name
Change "Field1" to your field name
Execute the query.

[ERF]

Thank you! Thank you! I did all as directed and I am getting a message that reads "Run-Time Error '13': Type Mismatch". After I select "end" I can see the results are what I wanted. However, the error message keeps popping up. Where did I go wrong? Also, I realized there were some other factors in the data that I was not accounting for. Some of the data has more than one decimal point (EX. 9.68.080) This data is not being read and shows as error. And, no space after the number(EX. 655.7(C)). It is also showing an error because there is no space between 7 and (C). How can I fix this?

Again thanks a million!

[orange]

Not sure exactly what you need. Here is a function to take in a string and output/return
only characters 0 thru 9 or . (decimal)
It returns all digits and decimal points

Function fKeepNumbers(strIN As String) As String
      Dim i As Integer
      Dim strOut As String
10       For i = 1 To Len(strIN) Step 1
20          If Mid(strIN, i, 1) Like "[0-9.]" Then
30             strOut = strOut & Mid(strIN, i, 1)
40          End If
50       Next i
60       fKeepNumbers = strOut

End Function

test routine

Sub testKeepNumbers()
          Dim x As String
10        x = "More than 11.9 minutes over "
20        Debug.Print fKeepNumbers(x)
30        MsgBox fKeepNumbers(x), vbOKOnly
End Sub

good luck.

[ERF]

I guess I should state what I am trying to do in its entirety; I have three tables: Codes, Customers, and Code points. My Customers table has codes, that should match the codes in my Codes table which will then give me an IBR Code which will match with my Code points table. In the end each code will have a point which I will tally up in the end. The problem is that the codes in my Codes table and Customer's table do not match exactly. I figured if I can pull only the numeric portion from each code I can relate the tables in that form. Am I making things more complicated? I'll give a visual example below in case I am not explaining myself correctly:

Codes Table	Customer Table
421 (A)(B)	421(A)(1)

(technically same code for my purposes, I really don't care about what is in parenthesis).

421=90z(IBR Code) and a 90z=2points. The point values range from .1-5 depending on IBR Code; to give me a total at the end.

I am sure I am making this harder than it should be, but after trying to figure it out I threw in the towel for some help. Please let me know if there is more info you need to clarify.

And again, thanks for any help!

[davegri]

Why can't it just be that 421 = 2 points? What does the IBR code have to do with it?

[orange]

I guess I should state what I am trying to do in its entirety;

Absolutely. That's the starting point for you and for readers. Get a clear picture of the issue/opportunity to be "resolved through some automation". If you don't know what you're doing, or you don't communicate the issue and some context to readers, it becomes a guessing game with a trial and error strategy. Get a clear statement of requirements and some meaningful test data.

Good luck.