Text

[mjbryant]

I have a file of about 5,000 records. They are people's names. Some of the records include characters such as " or , or # or 123... I want a file that has only text in it (A-Z)...how do I delete these records that are not pure text?

 

[mdmilner]

There isn't any single function that can test for alpha characters only like what is available for numbers (IsNumeric) or Dates (IsDate). But, what you can do is use the ASC function to evaluate the Ascii value of each character within a string.

You'll want to tweak this to match your needs.

Private Function DelNonText()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strVal, strSQL As String
Dim x As Integer

Set dbs = CurrentDb()
strSQL = "SELECT * FROM Table1"
Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)

With rs
  Do Until rs.EOF
    strVal = .Fields(1).Value
    For x = 1 To Len(strVal)
      Select Case Asc(Mid(strVal, x, 1))
        Case 65 To 90:       ' Upper Case Alphabet
        Case 97 To 122:      ' Lower Case Alphabet
        Case Else
          .Delete
          Exit For           ' Found one stop checking
      End Select
    Next x

    .MoveNext
  Loop
End With

Set rs = Nothing
Set dbs = Nothing
End Function

Mike