Check field format through VBA-code

[kuipers78]

I would like to use VBA-code to check if the input of a table field matches a specific format. The main formats are: only digits and only characters.

Example:

The input check is: only digits.

A table field "Phone Number" contains the value "12345678". VBA identifies this field only contains digits and nothing happens.

Now, the same table field "Phone Number" contains the value "1234ABCD". VBA identifies this field also contains characters and will return a self-defined error message.

Is this possible?

Any help will be greatly appreciated!

 

[freakazeud]

re:

Hi,
You could use the IsNumeric() function on the before update event of the control bound to your table field. E.g.:

If IsNumeric(Me.YourControl) = False Then
MsgBox("Not numeric...try again!")
Cancel = True
End If

If you want to prevent alphabetic characters right while the user is typing them you could also use the on keypress event of the control to check the ASCII character code.

HTH
Good luck

 

[Bat17]

I think that you may need to use Regular Expressions if you want anything more than simple checking.
see if this helps

Function TestString(strIn As String) As String
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
TestString = "No Match"
re.Pattern = "[^a-z]"
If Not re.test(strIn) Then
    TestString = "Lower case only"
End If
re.Pattern = "[^A-Z]"
If Not re.test(strIn) Then
    TestString = "Upper case only"
End If
re.Pattern = "[^0-9]"
If Not re.test(strIn) Then
    TestString = "Numbers only"
End If
Set re = Nothing
End Function

 Sub test()
 Debug.Print "TestString ('ddd')", TestString("ddd")
 Debug.Print "TestString ('DdD')", TestString("DdD")
  Debug.Print "TestString ('123')", TestString("123")
 Debug.Print "TestString ('123ddd')", TestString("123ddd")
 Debug.Print "TestString ('123ddd')", TestString("DDDDD")
End Sub

peter

 

[kuipers78]

Thanks for your reply Freakazeud! The IsNumeric-function works properly!

Maybe I wasn't clear enough, but I don't want to check a new value that is being added by the user (this could simply be checked in the field properties of the table). The field values are already there (through an import), regardless the format.
I want to make the check in VBA and generate a warning if certain imported values aren't completely numeric or alphabetical.

Anyway, this can be (partly) done by the function IsNumeric. So, thanks again!