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

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?
0
SteveL13
Asked:
SteveL13
  • 4
  • 2
  • 2
1 Solution
 
SteveL13Author Commented:
Meant to mention, I would like the invalid records to print to a report.
0
 
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
    FORMAT TEXT;
0
 
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";
Done!

Hope that helps
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeffrey CoachmanMIS LiasonCommented:
Thee are many functions available to validate email addresses
Here is a popular one:
http://www.experts-exchange.com/Database/MS_Access/Q_28090970.html#a39061427

JeffCoachman
0
 
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.
;-)

JeffCoachman
0
 
SteveL13Author Commented:
Hmm,

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.
'http://www.TechKnowledgeyInc.com

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
    Next
Else
    For i = 1 To Len(strCheck)
        If InStr(sInvalidChars, Mid(strCheck, i, 1)) > 0 Then
            bCK = False
            GoTo ExitFunction
        End If
    Next
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))
Else
    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))
    Else
        bCK = False
        GoTo ExitFunction
    End If
Loop

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

ExitFunction:
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"
0
 
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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