Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Non English/US characters (ACCESS 2003)

    I have a table of names which contain some foreign characters in the name, ie a letter I with 2 dots above. How can I run through such a table with a query that will identify names having a non uk letter?

    Many thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Non English/US characters (ACCESS 2003)

    I haven't tested this extensively, but you could copy the following function into a standard module:
    <code>Public Function IsForeign(varText) As Boolean
    Dim i As Integer
    If IsNull(varText) Then
    Exit Function
    End If
    For i = 1 To Len(varText)
    If AscW(Mid(varText, i, 1)) > 127 Then
    IsForeign = True
    Exit Function
    End If
    Next i
    End Function
    </code>
    Then create a query based on your table.
    Add the name field, and a calculated field:

    F: IsForeign([TheName])

    where TheName is the field containing the names.
    Clear the Show check box for this column.
    Enter True in the Criteria line for this column.
    This query will select all names containing a non-standard character.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Non English/US characters (ACCESS 2003)

    Note: you could also export the table to Excel and use formulas to detect non-standard characters. The array formula (confirmed with Ctrl+Shift+Enter)
    <code>
    =MAX(CODE(MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1)) )>127
    </code>
    will return TRUE if the text in A1 contains a non-standard character.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non English/US characters (ACCESS 2003)

    Many thanks Hans - did exactly what I wanted. Very much appreciated. Best Regards

Posting Permissions

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