Wednesday, November 02, 2005

Check for Numeric Values in a String using VBA

I needed to check that the values entered into a textbox called StoreID in an Access Database where numerical and composed of 3 digits. Here is one solution: on the AfterUpdate event of the textbox, put this code:

Private Sub StoreID_AfterUpdate()
Dim intLen, intChar, i As Integer
Dim strTextBox As String

intLen = Len(StoreID) 'Len is a function that counts the amount of characters in a string
If intLen <> 3 Then ' if the length is not 3, give message box
MsgBox "The field 'Store ID' requires 3 digits from 001 to 900"
Else ' if length is 3 then assign variables and start loop for checking numerical values
intLen = Len(Me!StoreID)
'Me! always refers to the active form
strTextBox = Me!StoreID
For i = 1 To 3 'For 1 to 3
intChar = Mid(strTextBox, i, 1) 'Mid function takes middle values (expression,start value, length of selection)
If IsNumeric(intChar) = False Then
MsgBox " ' " & intChar & " ' is not a number, please enter numeric values only from 001 to 900"
Exit Sub
End If
End If
End Sub

No comments: