Access 2010 Email Validation for form field


I have a customer details form where the email address entered should have one and only one "@" and that this is neither the first nor the last character.

I have used the following code to try to achieve this but it does not stop the @ sign being entered as the first or last character. It also allows more than 1 @ sign to be entered:

Is Null Or ((Like "*??@??*.??*") And (Not Like "*[ ,;]*"))

Any ideas anyone?
Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
There are dozens of popular way to validate email addresses
Have you searched here and on the web yet?

You also need to check that the "@" and a "." exist...

Try this as a start, (to get you what you asked for here specifically)

Private Sub email_BeforeUpdate(Cancel As Integer)
If Left(, 1) = "@" Or Right(, 1) = "@" Or CountOccurrences(, "@") > 1 Or CountOccurrences(, "@") = 0 Then
    MsgBox "Invaid email"
    Cancel = True
End If

End Sub

Function CountOccurrences(str, substring) As Long
'Function from here:
'From here:

'   Returns the number of times substring appears in str
    Dim x As Variant
    x = Split(str, substring)
    CountOccurrences = UBound(x)
End Function
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
andrewpiconnectAuthor Commented:
Hi Boag,

Your code works well, however, how can i check that there is at least 1 "." in the email address?
andrewpiconnectAuthor Commented:
To solve the missing "." I simply added "" Or CountOccurrences(Me.Email, "." = 0 ""
Jeffrey CoachmanMIS LiasonCommented:
Great, Glad I could help.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.