ID: Q114554
The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills.
This article shows you how to create a sample user-defined function called IsAlpha() that you can use to validate a string to make sure that every character in the string is a letter in 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. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0
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
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.
6. On the Records menu, click Data Entry.
7. Type "ABCD" (without the quotation marks) in the CategoryName
field.
Note that the validation rule allows you to create this entry and
to exit the field.
CategoryName field. Note that the validation rule does not accept the
entry.
For more information about string manipulation, search the Help Index for "strings, manipulating," or ask the Microsoft Access 97 Office Assistant.
Additional query words:
Keywords : kbprg kbusage
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbinfoLast Reviewed: November 20, 1998