Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2002
    Posts
    23

    Unanswered: Alpha and numeric search

    Hi,

    How do I look for record that have alpha numberic? Example,

    123456
    875B566
    35688W678
    41WM67999

    I only want those that have any alpha.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Not Isnumeric ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Oh - carefull

    What's the result of the following?

    ?IsNumeric("123e12")
    ?IsNumeric("1d23")

    Haven't tested, but could this be something?

    ?Not "123e12" Like "*[A-Z]*"
    ?Not "1d23" Like "*[A-Z]*"
    ?Not "123456" Like "*[A-Z]*"
    ?Not "35688W678" Like "*[A-Z]*"
    Roy-Vidar

  4. #4
    Join Date
    Mar 2002
    Posts
    23
    Kewl, this work perfectly. Thanks.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by RoyVidar
    Oh - carefull

    What's the result of the following?

    ?IsNumeric("123e12")
    ?IsNumeric("1d23")

    Haven't tested, but could this be something?

    ?Not "123e12" Like "*[A-Z]*"
    ?Not "1d23" Like "*[A-Z]*"
    ?Not "123456" Like "*[A-Z]*"
    ?Not "35688W678" Like "*[A-Z]*"
    Uh ... False and False ... IsNumeric is not like Val where it returns the numeric portion up to the 1st alpha character ... If the expression can represent a valid numeric value it returns true ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I know that IsNumeric is not the same as Val, and you probably have something wrong on your setup, cause the IsNumeric function is supposed to evaluate also "scientific notation" as true, which means that IsNumeric is intended to return true on

    ?IsNumeric("123e12")
    ?IsNumeric("1d23")

    E is single-precision floating point
    D is double-precision

    Here's a couple of reads
    http://groups.google.com/group/micro...22a5b9a03d45e/
    http://groups.google.com/group/micro...2b09cd3810524/
    Roy-Vidar

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by RoyVidar
    I know that IsNumeric is not the same as Val, and you probably have something wrong on your setup, cause the IsNumeric function is supposed to evaluate also "scientific notation" as true, which means that IsNumeric is intended to return true on

    ?IsNumeric("123e12")
    ?IsNumeric("1d23")

    E is single-precision floating point
    D is double-precision

    Here's a couple of reads
    http://groups.google.com/group/micro...22a5b9a03d45e/
    http://groups.google.com/group/micro...2b09cd3810524/
    Ahhhhh ... Not enough caffiene yet to catch that one ... Well my idea is shot down ... Good point there Roy.
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You'd have to create a function that iterates through each character in the string and checks to see whether it's alpha or numeric. If you get a hit for both on any given string, return true...

    Here's my homebrew IsAlpha() function too:

    Code:
    Public Function IsAlpha(strIn As String) As Boolean
    Dim ascIn As Integer
    Dim i As Integer
    
    IsAlpha = True
    For i = 1 To Len(strIn)
        ascIn = Asc(Mid(strIn, i, 1))
        If Not (ascIn >= 65 And ascIn <= 90) Or Not (ascIn >= 97 And ascIn <= 122) Then
            IsAlpha = False
            Exit Function
        End If
    Next i
    
    End Function
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have some RegEx:

    Code:
    Public Function IsAlphaNumeric(strIn As String) As Boolean
    Dim ret As Boolean
    If strIn Like "[A-Z]*[0-9]" Or strIn Like "[0-9]*[A-Z]" Then
        ret = True
    Else
        ret = False
    End If
    
    IsAlphaNumeric = ret
    End Function
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Since RegExp is mentioned, though RegExp is probably far from ideal for this challenge
    Code:
    ' Declaration section
    Public gre                  As Object
    
    ' Function
    Function IsAlphaRegExp(ByVal v_strIn As String) As Boolean
        If (gre Is Nothing) Then
            Set gre = CreateObject("vbscript.regexp")
        End If
        With gre
            .IgnoreCase = True
            .Pattern = "(\d+[a-z]+)|([a-z]+\d+)"
            IsAlphaRegExp = .test(v_strIn)
        End With
    End Function
    Should require at least one letter and one digit, regardless of order.
    Roy-Vidar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •