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

My favorite use of Split. A one-liner that gets the job done with no need for an intermediate variant. :-)

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, May 17, 2012 3:31 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] How to Eliminate a Numeric String from a Field in anAccess Query

Hi Arthur

Given the examples, Split could do it:

strFirst = Split(strValue, " ")(0)

/gustav


>>> fuller.artful at gmail.com 17-05-12 0:03 >>>
Jim,

That's only going to work for one specific example. But assuming that your examples are accurate, that is, an indefinite number of alpha chars are perhaps followed by an indefinite number of numeric chars, then the following should work:

<vba>
Public Function AlphaPart(s As String)
    Dim i As Integer
    Dim j As Integer
    Dim strAlphaPart As String
    j = Len(s)
    For i = 1 To j
        Debug.Print Mid(s, i, 1), IsNumeric(Mid(s, i, 1))
        If IsNumeric(Mid(s, i, 1)) Then  'Bingo!
            strAlphaPart = Left(s, i - 1)
            Exit For
        End If
    Next
    AlphaPart = strAlphaPart
End Function

Example:
? AlphaPart("Arthur 123")
'returns "Arthur"

I tested it against a few strings and it seems to work as advertised.
HTH,

--
Arthur
Cell: 647.710.1314

Prediction is difficult, especially of the future.
  -- Niels Bohr

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com