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...

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.

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

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

Validation code (http://www.access-programmers.co.uk/forums/showthread.php?t=55376)

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. :)

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 FunctionI'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 gillAlso note that a correct validation of an e-mail address, is a tad more complex than any of the suggestions here.