Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 15th, 2005, 11:56 AM
Friend of Wrox
 
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Corey
Default Remove letters from numbers

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

 
Old December 15th, 2005, 04:40 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old December 15th, 2005, 04:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

Dim varDate As Variant

Bob

 
Old December 15th, 2005, 05:19 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old December 16th, 2005, 10:40 AM
Friend of Wrox
 
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Corey
Default

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


 
Old December 16th, 2005, 12:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old December 18th, 2005, 05:27 PM
Friend of Wrox
 
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Corey
Default

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

 
Old December 18th, 2005, 09:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Your welcome, Corey. Thanks for the topic.

Bob





Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove numbers from cell EricB123 Excel VBA 25 August 6th, 2014 01:55 PM
How to remove numbers in XML files using xsl srkumar XSLT 1 April 15th, 2008 06:43 AM
Compare numbers and letters in same cell EricB123 Excel VBA 1 January 21st, 2007 03:30 PM
fillin array with letters from a to Z and numbers sajid C# 10 May 3rd, 2005 03:38 PM
Taking both numbers and letters as input HateMe C# 1 May 13th, 2004 11:33 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.