[AccessD] How to Eliminate a Numeric String from a Field in an Access Query

Charlotte Foust charlotte.foust at gmail.com
Wed May 16 18:09:03 CDT 2012


Here's a quick routine to parse the alpha characters out of a string:

Public Function ParseAlpha(ByVal strIn As String) As String
' Charlotte Foust 16-May-2012
    Dim strOut As String
    Dim c As String
    Dim i As Integer

    For i = 1 To Len(strIn)
        c = Mid$(strIn, i, 1)
        If Not IsNumeric(c) Then
            strOut = strOut & Trim(c)
        End If
    Next i
    ParseAlpha = strOut

End Function
ParseAlpha("ABC123 def45") returns "ABCdef"

Charlotte

On Wed, May 16, 2012 at 3:20 PM, Charlotte Foust
<charlotte.foust at gmail.com>wrote:

> Will the values follow a specific pattern, i.e. characters then numbers?
> If so  you could write a quick function to start at the end of the string
> and test each character until it hits an alpha, then return the left part
> of the string up to that point.
>
> Charlotte Foust
>
> On Wed, May 16, 2012 at 12:44 PM, Brad Marks <BradM at blackforestltd.com>wrote:
>
>> We are pulling data from a purchased package via an Access 2007 Query.
>>
>> One of the fields has a combination of Alphabetic characters and
>> numbers, such as these examples.
>>
>> ABC 1234
>> BB  1234556
>> CCC 55
>>
>> Is there a way to eliminate the numeric part of the strings in these
>> records within an Access Query?
>>
>> "ABC 1234" would become ABC
>> "BB  1234556" would become BB
>> "CCCDD 55" would become CCCDD
>>
>> Thanks,
>> Brad
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>>
>>
>>
>> Website: http://www.databaseadvisors.com
>>
>>
>>
>>
>


More information about the AccessD mailing list