ACC: Validation Rule to Accept Only Alphabetical Characters

MORE INFORMATION

The IsAlpha() function accepts a string argument and checks each character in the string to make sure that it is a character in the English alphabet. The function does this by checking the ASCII value of each character in the string to see if it falls in the range from 65 to 90 (for uppercase characters), or in the range from 97 to 122 (for lowercase characters), of the ANSI character set. The function returns True if all the characters in the string fall within one of these two ranges, or False if one or more characters in the string do not fall within one of these two ranges. You can use this function as a validation rule for a field in a table in Microsoft Access version 1.x, or as a validation rule for a control on a form in Microsoft Access 1.x, 2.0, 7.0, and 97.

The following example shows you how to use the function as a validation rule for a control on a form.

CAUTION: Following the steps in this example will modify the sample database Northwind.Mdb (NWIND.MDB in 1.x or 2.0). You may want to back up the Northwind.Mdb file, or perform these steps on a copy of the Northwind database.

1. Open the sample database Northwind.mdb (or NWIND.MDB).
2. Create a new module and enter the following code:

        '**********************************
         'Declarations section of the module
         '**********************************
         Option Compare Database
         Option Explicit
   
         '===============================================================
         ' The following function is named IsAlpha(). It accepts a string
         ' argument and returns either True (-1) or False (0).
         '===============================================================
   
         Function IsAlpha (MyString As String) As Integer
         Dim LoopVar As Integer
         Dim SingleChar As String
   
         LoopVar = 1
   
         If IsNull(MyString) Then
            IsAlpha = False
            Exit Function
         End If
   
         For LoopVar = 1 To Len(MyString)
            SingleChar = UCase(Mid$(MyString, LoopVar, 1))
            If SingleChar < "A" Or SingleChar > "Z" Then
               IsAlpha = False
               Exit Function
            End If
         Next LoopVar
   
         IsAlpha = True
   
         End Function
   						

3. Open the Categories form in Design view.
4. Set the CategoryName field's ValidationRule property to:

         IsAlpha([CategoryName]) = True
   						

   NOTE: In versions 1.x and 2.0, there is a space in Category Name.
5. Save the Categories form, and then view the form in Form view.
6. On the Records menu, click Data Entry.
7. Type ABCD in the CategoryName field.
   
   Note that the validation rule allows you to create this entry and to exit the field.
8. In another new record, type AB,D in the CategoryName field. Note that the validation rule does not accept the entry.