One option is to systematically check for the various dos and don’t using a series of if statements such as that in the function below:
Public Function isValidEmail(inEmailAddress As String) As Boolean ' Author: Unknown If (Len(inEmailAddress) = 0) Then MsgBox "Please enter your email address." isValidEmail = False Exit Function End If If (InStr(1, inEmailAddress, "@") = 0) Then MsgBox "The '@' is missing from your e-mail address." isValidEmail = False Exit Function End If If (InStr(1, inEmailAddress, ".") = 0) Then MsgBox "The '.' is missing from your e-mail address." isValidEmail = False Exit Function End If If (InStr(inEmailAddress, "@.") > 0) Then MsgBox "There is nothing between '@' and '.'" isValidEmail = False Exit Function End If If ((InStr(inEmailAddress, ".")) = ((Len(inEmailAddress)))) Then MsgBox "There has to be something after the '.'" isValidEmail = False Exit Function End If If ((Len(inEmailAddress)) < (InStr(inEmailAddress, ".") + 2)) Then MsgBox "There should be two letters after the '.'" isValidEmail = False Exit Function End If If (InStr(1, inEmailAddress, "@") = 1) Then MsgBox "You have to have something before the '@'" isValidEmail = False Exit Function End If isValidEmail = True End Function |
A Second better approach, like with many web programming languages, is to use regular expressions to validate certain type of strings. Now Access’ VBA does not allow RegEx persey, but you can very easily access such functionality by adding 2 simple lines of code. You then end up with a total of a three lines of code to validate almost any string, including an e-mail address. For all the details, and a link to where you can get a multitude of the RegEx so you don’t have to reinvent the wheel simply check out my post VBA – Using Regular Expressions (RegEx)
