Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Criteria to exclude numbers (2003 Sp2)

    Is it possible to have a query field criteria which will either exclude records in a field containing numbers only or alternatively filter the same field containing letters only? What would it be? Thanks

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Criteria to exclude numbers (2003 Sp2)

    You can use the function isnumeric to test whether a field contains only numbers.

    The example below would return records where the LotNo field did not contain only numbers.

    This is not the same as contains only letters. If it has a mixture of letters and numbers, or some other characters again, then isnumeric would be false.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria to exclude numbers (2003 Sp2)

    Yes I should have stated that the field contains both numbers and letters but I want to see only records where this field contains letters. Thanks.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria to exclude numbers (2003 Sp2)

    I got it ...

    Like "*[!0-999]*"

    Thanks.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Criteria to exclude numbers (2003 Sp2)

    The only way I can think of is to create a custom function.

    Create a new module and paste this text into it. You can then use the function isalpha in queries the same way as the isnumeric function.

    This is likely to be slow however!
    <pre>Public Function isAlpha(Txt As String) As Boolean
    ' Txt = text to be parsed

    Dim n As Integer
    Dim m As Integer
    Dim blisalpha As Boolean
    blisalpha = True
    For n = 1 To Len(Txt)
    m = Asc(Mid(Txt, n, 1))
    'Debug.Print m
    If Not (((m > 64) And (m < 91)) Or ((m > 96) And (m < 123))) Then
    blisalpha = False
    End If

    Next n
    ' Debug.Print blisalpha
    isAlpha = blisalpha

    End Function
    </pre>



    edited to correct a wrong type
    Regards
    John



Posting Permissions

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