Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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

Reply With Quote
  #2 (permalink)  
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

Reply With Quote
  #3 (permalink)  
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

Reply With Quote
  #4 (permalink)  
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
Reply With Quote
  #5 (permalink)  
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


Reply With Quote
  #6 (permalink)  
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

Reply With Quote
  #7 (permalink)  
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

Reply With Quote
  #8 (permalink)  
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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:39 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.