Validation for Entering an Email Address

[swedish_mania]

Hey all,

What is the easiest way to create a validation rule for entering email addresses i.e. making sure the @ symbol is between text.

e.g. texthere@texthere.com

Thanks in advance...

 

[M8KWR]

Give this a try

In your field that you want to validate the entry, in the after update procedure type this

If RemoveAlphas(FIELD_NAME) = "Yes" Then

'email is ok

Else

MsgBox "Incorrect email address please enter a valid one" 'email does not contain the @ symbol

End If

Now create a module and paste this into it

Function RemoveAlphas(ByVal AlphaNum As Variant)

   Dim Clean As String
   Dim Pos, A_Char$
   Dim EmailSymbol

   Pos = 1
   If IsNull(AlphaNum) Then Exit Function

   For Pos = 1 To Len(AlphaNum)
      A_Char$ = Mid(AlphaNum, Pos, 1)
      If A_Char$ = "@" Then
         
  Clean$ = "Yes"
      End If
      
      
   Next Pos



   RemoveAlphas = Clean$

End Function

I have modified the module for your request, but it does all work - just change your message etc.

Any issues please let me know.

 

[swedish_mania]

Now create a module and paste this into it

how do i create a module, that isnt an event procedure? How does this link to the field i want it to?

Probably basic stuff, but im a little unsure what you mean...

Thanks for you help

 

[Mile-O]

Don't do your validation in a table; do it on a form.

Validation code

 

[swedish_mania]

Thanks SJ McAbney, that code worked a treat.

It doesnt check for invalid characters, but it does what i want it to, for testing so im happy, thanks again, your help is much appreciated.

 

[RoyVidar]

Should you wish to persue further validation of the e-mail address, perhaps usage of Regular Expressions might provide what you need?

Private mre As Object

Public Function ValidateEmailAddress(ByVal v_strIn As String) As Boolean
    
    If (mre Is Nothing) Then
        ' instantiate Regular Expression object
        Set mre = CreateObject("vbscript.regexp")
    End If
    mre.Pattern = "^(([a-zA-Z0-9]+_+)|([a-zA-Z0-9]+\-+)|" & _
                "([a-zA-Z0-9]+\.+)|([a-zA-Z0-9]+\++))*" & _
                "[a-zA-Z0-9]+@((\w+\+)|(\w+\.))*\w{1,63}" & _
                "\.[a-zA-Z0-9]{2,6}$"
    ValidateEmailAddress = mre.test(v_strIn)
    
End Function

I've just taken one of the patterns from http://regexlib.com/Default.aspx (click the Pattern link).

I also just downloaded The Regulator, wich is presented on the main page, and it seems like a good tool to experiement with Regular Expressions.

It may look a bit complicated, but there are pages on the above linked site which explains the different tokens and characters. One can of course do similar testing through lot's of InStr, looping the string - and don't forget the Like operator in VB/VBA..., but Regular Expressions offer the possiblity of more excessive testing in one go.

Some short explanations of some of the symbols

^ - start of string
$ - end of string
* - 0 or more of previous

    [a-zA-Z0-9]+_+
[] -explicit set of characters to match (as can be used by the VB Like operator)
+ - 1 or more of previous
_ - underscore;)
 -> the pattern allows only valid characters at the start of the 
    string, but underscore may occur somewhere after the first 
    character. The last "+" will also allow for more than one 
    consecutive underscore.

    \w{1,63}
\w - any word character - equivalent with [a-zA-Z_0-9]
{1,63} - at least one, max 63 occurances
| - alternation (bill|ted) -> will allow both bill and ted, but not gill

Also note that a correct validation of an e-mail address, is a tad more complex than any of the suggestions here.