Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 937
  • Last Modified:

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?
  • 2
  • 2
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Jeffrey CoachmanCommented:
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(Me.email, 1) = "@" Or Right(Me.email, 1) = "@" Or CountOccurrences(Me.email, "@") > 1 Or CountOccurrences(Me.email, "@") = 0 Then
    MsgBox "Invaid email"
    Cancel = True
End If

End Sub

Function CountOccurrences(str, substring) As Long
'Function from here:
'From here: http://spreadsheetpage.com/index.php/site/tip/the_versatile_split_function/

'   Returns the number of times substring appears in str
    Dim x As Variant
    x = Split(str, substring)
    CountOccurrences = UBound(x)
End Function
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 CoachmanCommented:
Great, Glad I could help.


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now