Remove letters from numbers

Corey · December 15th, 2005, 11:56 AM

Hello again.

Iâm trying to find a way in a query to remove any data except for numbers

Examples

NET 90
NET 10
NET 10 DAYS
NET 110
NET 15

Any help would be greatly appreciated

Corey

Bob Bedell · December 15th, 2005, 04:40 PM

Hi Corey,

This should get âer done. Sub UpdateFieldValue creates an ADO recordset based on your table (tblData in my example) and passes the field value (your alphanumeric string) to a function called SplitString. SplitString creates an array of elements representing the portions of your string delimited by â â. The IsNumeric function then checks to see if each array element can be evaluated as a number. If it can, SplitString returns the numeric expression and exits. The return value is then used to update the ADO recordset, and the process loops again for subsequent records. If all your data is in the format you posted, this should work for you.

Code:

Public Sub UpdateFieldValue()
    On Error GoTo ErrorHandler

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim varDate As Variant
    Dim strFieldValue As String
    Dim strNumericExpression As String

    Set cnn = CurrentProject.Connection

    Set rst = New ADODB.Recordset
    rst.Open "tblData", cnn, adOpenKeyset, adLockOptimistic, adCmdTable


     With rst
        .MoveFirst
        Do Until .EOF
            ' Assign the first records data field
            ' to a variable, then change the field value
            strFieldValue = rst!Data
            Debug.Print "Original data: " & rst!Data
            strNumericExpression = SplitString(strFieldValue)

            If Not IsNull(strNumericExpression) Then
                rst!Data = strNumericExpression
                rst.Update
                Debug.Print "Changed data: " & rst!Data
            End If
            .MoveNext
        Loop
    End With

    ' clean up
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

ErrorHandler:
    ' clean up
    If Not rst Is Nothing Then
        If rst.State = adStateOpen Then rst.Close
    End If
    Set rst = Nothing

    If Not cnn Is Nothing Then
        If cnn.State = adStateOpen Then cnn.Close
    End If
    Set cnn = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub

Public Function SplitString(strString As String) As String
    Dim avarStringArray As Variant
    Dim intIndex As Integer
    Dim blnNumeric As Boolean

    avarStringArray = Split(strString, " ")

    'Can array element be evaluated as a number?
    For intIndex = 0 To UBound(avarStringArray)
        blnNumeric = IsNumeric(avarStringArray(intIndex))

        If blnNumeric Then
            SplitString = avarStringArray(intIndex)
            Exit Function
        End If

    Next intIndex

End Function

HTH,

Bob

Bob Bedell · December 15th, 2005, 04:44 PM

Delete the following variable declaration. Just pasted accidentally from something I posted a while bac. Don't need it:

Dim varDate As Variant

Bob

SerranoG · December 15th, 2005, 05:19 PM

Corey, the Val function will strip the non-numeric parts of a field and just give you the numbers.

Val("NET 10 DAYS") = 10
Val("NET 110") = 110

See help on Val for details.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Corey · December 16th, 2005, 10:40 AM

I'm sorry Bob, What did you mean that I should delete the following variable declaration?

Also, will this code keep fields that donât have any numeric data?

Example

COD
PPD
50% 10

Thanks

Corey

Bob Bedell · December 16th, 2005, 12:34 PM

Hi Corey,

Fisrt a note on the VAL Function. Unfortunately, it stops reading a string at the first character it can't recognize as a number, so:

Val("NET 10 DAYS") would return 0, not 10.

On the finding of alphabetic substrings...

At the top of the UpdateFieldValue routine you'll see the line:

Dim varDate As Variant

Just delete it. The code doesn't use that variable.

If you want to evaluate for alphabetic instead of numeric expressions, find the following line in the SplitString routine:

Code:

blnNumeric = IsNumeric(avarStringArray(intIndex))

and add the NOT operator:

Code:

blnNumeric = Not IsNumeric(avarStringArray(intIndex))

Also, rename the variable blnNumeric to blnAlphabetic so the code more accurately reflects whats going on.

The Split function takes a string like "NET 10 DAYS" and stores the delimited portion of the string in a variant array like:

avarStringArray(0) = NET
avarStringArray(1) = 10
avarStringArray(2) = DAYS

The line blnAlphabetic = Not IsNumeric(avarStringArray(intIndex)) would evaluate each array element to see if it contains only alphabetic charaters. The code exits, however, after finding the fist element that contains alphabetic characters. So the code output (and the final state of your table with the values updated) would look like:

Original data: Net 10 Days
Changed data: Net
Original data: 100% 10
Changed data: 100%
Original data: Net 30
Changed data: Net

If you wanted the code to pick up both alphabetic expressions in a string like "Net 10 Days", the code would need some revision.

HTH,

Bob

Corey · December 18th, 2005, 05:27 PM

Hi Bob, Thanks you very much. The code worked perfect. I'm able to use both codes that you showed me.

Thank you again

Corey

Bob Bedell · December 18th, 2005, 09:09 PM

Your welcome, Corey. Thanks for the topic.

Bob