Microsoft KB Archive/210385

Knowledge Base

ACC2000: Validation Rule to Accept Only Alphabetical Characters

Article ID: 210385

Article Last Modified on 6/23/2005

---

APPLIES TO

• Microsoft Access 2000 Standard Edition

---

This article was previously published under Q210385

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SUMMARY

This article shows you how to create a sample user-defined function that you can use to validate a string to make sure that every character in the string is a letter of the English alphabet.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

The following example creates a function named IsAlpha(), which accepts a string argument and checks each character in the string to make sure that it is a character of 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). 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 control on a form.

To create the sample function, follow these steps:

1. Start Microsoft Access, and then open the sample database Northwind.mdb or the sample project NorthwindCS.adp.
2. In the Database window, click Modules, and then click New.

3. Type or paste the following code in the Declarations section of the module sheet:

   Option Compare Database
   Option Explicit
                       

4. Type or paste the following code in the module to create a function named IsAlpha():

   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
                       

5. On the File menu, click Close and Return to Microsoft Access.
6. In the Database window, click Forms, click Categories, and then click Design.
7. Set the CategoryName field's ValidationRule property to:

   IsAlpha([CategoryName]) = True

8. On the File menu, click Save, and then on the View menu, click Form View.
9. On the Records menu, click Data Entry.
10. Type ABCD in the Category Name box.
    
    Note that the validation rule allows you to create this entry and to exit the field when you press ENTER.
11. On the Records menu, click Data Entry.
12. Type AB,D in the Category Name box.
    
    Note that when you press ENTER, the following message appears:

    The value you entered doesn't meet the validation rule defined for the field or control.

REFERENCES

For more information about how to use the sample code in this article, click the article number below to view the article in the Microsoft Knowledge Base:

212536 OFF2000: How to Run Sample Code from Knowledge Base Articles

For more information about validation rules, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type validationrule, validationtext properties in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Keywords: kbhowto kbinfo kbprogramming kbusage KB210385

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.