Loop through records to identify invalid email addresses

I inherited a database with a table that contains records of individuals.  In the table is a field named "Email".  I would like to somehow loop through the table and identify those records that appear to have an invalid email address.  In other words perhaps the @ is missing or the "." is missing or there is a space somewhere in the string of characters, etc.

Has anyone done this before?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveL13Author Commented:
Meant to mention, I would like the invalid records to print to a report.
Guru JiCommented:
You can just run a query inside that table

SELECT * FROM EmailTable WHERE email NOT LIKE '%_@__%.__%'

Anything more complex then this will run very slow or might not work properly.

You can thus save the output to a csv or txt file for the report purposes.
SELECT * FROM EmailTable WHERE email NOT LIKE '%_@__%.__%'
OUTPUT TO InvalidEmails.txt
Guru JiCommented:
Sorry for the output above I thought you are using SQL server, but for the access database you can follow the steps below to create a report from the dataset above
First Create a blank report;
Go to Property Sheet;
Choose "Data", select "Record Source" and choose your query;
Save your report;
Open report in Design View;
Select "Add Existing Fields";
In the bottom of the Field List select option "Show only fields in the current record source";

Hope that helps
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
Thee are many functions available to validate email addresses
Here is a popular one:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
The code above will simply validate the email.
So you really don't need to loop the records until you first identity the invalid emails (with this function)

Simply use it in a query:
SELECT ID, Email, ValidEmail([Email]) AS IsValidEmail
FROM YourTable;

Once the invalid emails are identified,(IsValidEmail=0),
...you can then loop this query  to fix only the invalid emails.

Actually "fixing" the emails will be a totally separate topic/question.

SteveL13Author Commented:

I copy/pasted the module code:

Option Compare Database

Function ValidEmail(ByVal strCheck As String) As Boolean
'Created by Chad M. Kovac
'Tech Knowledgey, Inc.

Dim bCK As Boolean
Dim strDomainType As String
Dim strDomainName As String
Const sInvalidChars As String = "!#$%^&*()=+{}[]|\;:'/?>,< "
Dim i As Integer

bCK = Not InStr(1, strCheck, Chr(34)) > 0 'Check to see if there is a double quote
If Not bCK Then GoTo ExitFunction

bCK = Not InStr(1, strCheck, "..") > 0 'Check to see if there are consecutive dots
If Not bCK Then GoTo ExitFunction

' Check for invalid characters.
If Len(strCheck) > Len(sInvalidChars) Then
    For i = 1 To Len(sInvalidChars)
        If InStr(strCheck, Mid(sInvalidChars, i, 1)) > 0 Then
            bCK = False
            GoTo ExitFunction
        End If
    For i = 1 To Len(strCheck)
        If InStr(sInvalidChars, Mid(strCheck, i, 1)) > 0 Then
            bCK = False
            GoTo ExitFunction
        End If
End If

Dim intAtLocation As Integer
Dim strSuffix As String
If InStr(1, strCheck, "@") > 1 Then 'Check for an @ symbol
    intAtLocation = Len(Left(strCheck, InStr(1, strCheck, "@") - 1))
    bCK = intAtLocation > 0
    strSuffix = Right(strCheck, Len(strCheck) - intAtLocation)
    bCK = bCK And InStr(1, strSuffix, ".") > 0 And (InStr(1, strSuffix, ".") < Len(strSuffix))
    bCK = False
End If

If Not bCK Then GoTo ExitFunction

strCheck = Right(strCheck, Len(strCheck) - InStr(1, strCheck, "@"))
bCK = Not InStr(1, strCheck, "@") > 0 'Check to see if there are too many @'s
If Not bCK Then GoTo ExitFunction

strDomainType = Right(strCheck, Len(strCheck) - InStr(1, strCheck, "."))
bCK = Len(strDomainType) > 0 And InStr(1, strCheck, ".") < Len(strCheck)
If Not bCK Then GoTo ExitFunction

strCheck = Left(strCheck, Len(strCheck) - Len(strDomainType) - 1)
Do Until InStr(1, strCheck, ".") <= 1
    If Len(strCheck) >= InStr(1, strCheck, ".") Then
        strCheck = Left(strCheck, Len(strCheck) - (InStr(1, strCheck, ".") - 1))
        bCK = False
        GoTo ExitFunction
    End If

If strCheck = "." Or Len(strCheck) = 0 Then bCK = False

ValidEmail = bCK
End Function

Open in new window

Then here is my SQL in a query I created:

SELECT Idee, Brokers.Email, ValidEmail([Email]) AS IsValidEmail
FROM Brokers;

But when I run the query I get an error:

"Unidentified function: 'ValidEmail' in expression"
Jeffrey CoachmanMIS LiasonCommented:
That code should be located in a Module
code in a Moule
Then run the compact/repair utility...
Then click Debug-->Compile (from the vba editor), ...to be sure
Jeffrey CoachmanMIS LiasonCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.