Access 2010 Email Validation for form field

Posted on 2012-09-05
Last Modified: 2012-09-05

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?
Question by:andrewpiconnect
    LVL 84
    LVL 74

    Accepted Solution

    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

    Author Comment

    Hi Boag,

    Your code works well, however, how can i check that there is at least 1 "." in the email address?

    Author Closing Comment

    To solve the missing "." I simply added "" Or CountOccurrences(Me.Email, "." = 0 ""
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Great, Glad I could help.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now