Doing Email Validations for Microsoft Access?

Question

I am currently working on an Access project, and I am having issues with validating the Email Field for my project. I want the emails to have a mandatory string, a mandatory @ symbol, and mandatory letters and numbers after the @ symbols.

Currently, my validation looks like this:

Like "* @ *"

This works perfectly with what I want; however, it still accepts entries that do not have letters, numbers, a period and dash beforehand. Any tips and suggestions on how to go about this and any resources where I can learn validation?

Google for `Access VBA password validation`. That should get you some results can use as a guide. — June7, Jan 22 '19 at 19:59
Possible duplicate of [Extensive Email validation for MS access table](https://stackoverflow.com/questions/50100482/extensive-email-validation-for-ms-access-table) — Rene, Jan 22 '19 at 20:28

score 1 · Answer 1 · answered Jan 23 '19 at 12:25

Another option to use Regular expressions. You can easily create your own pattern rather than using tones of ifs.

something like this:

Public Function FN_REGEXP_IS_EMAIL(email As String) As Boolean

    If IsBlank(email) Then Exit Function

    Const emailPattern As String = "^([\w\-\.]+)@((\[([0-9]{1,3}\.){3}[0-9]{1,3}\])|(([\w\-]+\.)+)([a-zA-Z]{2,4}))$"

    On Error Resume Next
    With CreateObject("vbscript.RegExp")
        .Pattern = emailPattern
        FN_REGEXP_IS_EMAIL = .test(email)
    End With

End Function

score 0 · Answer 2 · answered Jan 22 '19 at 22:27

If the validation occurs during manual entry of the email - then you can use a Mask. These are a text field property you'll want to read about online.

If the validation is needed on an existing set of data, not during the input phase, - or if you find that a Mask just is not suitable for some reason - then you'll need custom queries & code to check - and there is no single answer, it has to be crafted to meet all your requirements..

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

Try:

 like "*[@]*[.]*"

The above means "some chars - any kind", then MUST have a @, then "some chars - any kind", then MUST have a . (dot) Then some chars - any kind.

Edit

To force having characters in each part, use this:

like "?*[@]?*[.]?*"

So above is:

must have at least one char (?), 
then any number of chars (*),
then MUST have a @ sign ([@]),
then must have at least one char (?),
then any number of chars (*),
then must have a dot ([.]),
then must have one char (?),
then any number of chars (*)

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 23 '19 at 02:49

Albert D. Kallal

42,205
3
34
51

Thank you! It does work, except it accepts entries that do not contain characters before the @ sign – Learner Jan 23 '19 at 18:19
EDIT: I added a change in that you MUST enter characters between each part. – Albert D. Kallal Jan 23 '19 at 19:23

score 0 · Answer 4 · answered Jan 23 '19 at 08:33

We use this function - which is readable:

Public Function IsEmailAddress( _
    ByVal strEmailAddresses As String) _
    As Boolean

' Checks if strEMailAddr could represent one or more valid e-mail addresses.
' Does not check validity of domain names.
'
' 2003-06-22. Cactus Data ApS, CPH
' 2018-12-01. Expanded to allow for and validate multiple addresses.

  ' Allowed characters.
  Const cstrValidChars    As String = "@_-.0123456789abcdefghijklmnopqrstuvwxyz"
  Const cstrDot           As String = "."
  Const cstrAt            As String = "@"
  ' Minimum length of an e-mail address (a@a.ca).
  Const cintAddressLenMin As Integer = 6
  ' Address separator.
  Const cstrSeparator     As String = ";"

  Dim avarAddresses       As Variant
  Dim Index               As Integer
  Dim strEmailAddr        As String
  Dim strValidChars       As String
  Dim booFailed           As Boolean
  Dim intPos              As Integer
  Dim intI                As Integer

  avarAddresses = Split(strEmailAddresses, cstrSeparator)
  For Index = LBound(avarAddresses) To UBound(avarAddresses)
    strEmailAddr = avarAddresses(Index)
    ' Strip a display name.
    CleanEmailAddress strEmailAddr
    ' Convert to lowercase.
    strEmailAddr = LCase(strEmailAddr)
    ' Check that strEMailAddr contains allowed characters only.
    For intI = 1 To Len(strEmailAddr)
      If InStr(cstrValidChars, Mid(strEmailAddr, intI, 1)) = 0 Then
        booFailed = True
      End If
    Next
    If booFailed = False Then
      ' Check that the first character is not cstrAt.
      booFailed = Left(strEmailAddr, 1) = cstrAt
      If booFailed = False Then
        ' Check that the first character is not a cstrDot.
        booFailed = Left(strEmailAddr, 1) = cstrDot
        If booFailed = False Then
          ' Check that length of strEMailAddr exceeds
          ' minimum length of an e-mail address.
          intPos = Len(strEmailAddr)
          booFailed = (intPos < cintAddressLenMin)
          If booFailed = False Then
            ' Check that none of the last two characters of strEMailAddr is a dot.
            booFailed = (InStr(intPos - 1, strEmailAddr, cstrDot) > 0)
            If booFailed = False Then
              ' Check that strEMailAddr does contain a cstrAt.
              intPos = InStr(strEmailAddr, cstrAt)
              booFailed = (intPos = 0)
              If booFailed = False Then
                ' Check that strEMailAddr does contain one cstrAt only.
                booFailed = (InStr(intPos + 1, strEmailAddr, cstrAt) > 0)
                If booFailed = False Then
                  ' Check that the character leading cstrAt is not cstrDot.
                  booFailed = (Mid(strEmailAddr, intPos - 1, 1) = cstrDot)
                  If booFailed = False Then
                    ' Check that the character following cstrAt is not cstrDot.
                    booFailed = (Mid(strEmailAddr, intPos + 1, 1) = cstrDot)
                    If booFailed = False Then
                      ' Check that strEMailAddr contains at least one cstrDot
                      ' following the sign after cstrAt.
                      booFailed = Not (InStr(intPos, strEmailAddr, cstrDot) > 1)
                    End If
                  End If
                End If
              End If
            End If
          End If
        End If
      End If
    End If
    If booFailed = True Then
      Exit For
    End If
  Next

  IsEmailAddress = Not booFailed

End Function

Also, it allows for multiple addresses, like:

"joe@example.com;ann@domain.org"

Further, if addresses are copy-pasted, these may be extended with a leading display name. That is allowed as well, as the addresses are "cleaned" before validating using this function:

' Strips a full e-mail address with display name like:
'
'   "John Doe <john.doe@example.com>"
'
' to the e-mail address only:
'
'   "john.doe@example.com"
'
' 2018-12-05. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub CleanEmailAddress(ByRef EmailAddress As String)

    If Trim(EmailAddress) = "" Then
        EmailAddress = ""
    Else
        EmailAddress = Split(StrReverse(Split(StrReverse(EmailAddress), "<")(0)), ">")(0)
    End If

End Sub

Doing Email Validations for Microsoft Access?

4 Answers4

Edit

Linked