Access 2010 Email Validation for form field

Hi,

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?
andrewpiconnectAsked:
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(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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
0
 
andrewpiconnectAuthor Commented:
Hi Boag,

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

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