Solved

How to extract an email address from a description via a query in MS Access

Posted on 2015-02-12
5
128 Views
Last Modified: 2015-02-15
Hello

Does anyone know how to extract an email address from a field via a query?

I have a list of descriptions and some include an email address somewhere in the text and I am trying to extract just the email address, as I don't do code I would like to be able to do this in a query if possible, can anyone help?

Thanks
0
Comment
Question by:LillyC
  • 2
  • 2
5 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
While you could do this in a query, it would be easier to create a function and pass the text field to the function.  Something like:

Public Function fnEmailFromText(SomeValue As Variant) As Variant

    'Pass and return Variant to allow for NULL fields and to return a NULL value
    'if a valid email syntax is not found.
    'This function will only extract the first string that looks like a valid email string

    Dim intAtPosn As Integer, intStart As Integer, intEnd As Integer
    Dim strEmail As String

    If IsNull(SomeValue) Then
        fnEmailFromText = Null
        Exit Function
    ElseIf InStr(SomeValue, "@") = 0 Then
        fnEmailFromText = Null
        Exit Function
    End If
    
    intStart = 1
    intAtPosn = InStr(intStart, SomeValue, "@")
    Do While intAtPosn <> 0
    
        intStart = InStrRev(" " & SomeValue, " ", intAtPosn)
        intEnd = InStr(intAtPosn, SomeValue & " ", " ")
        
        strEmail = Mid(SomeValue, intStart, intEnd - intStart)
        If strEmail Like "*[@]*[.]*" Then
            fnEmailFromText = strEmail
            Exit Function
        End If
        intAtPosn = InStr(intAtPosn + 1, SomeValue, "@")

    Loop
    
End Function

Open in new window

Then, in your query you would use something like:

SELECT fnEmailFromText([SomeField]) as Email
FROM yourTable
WHERE fnEmailFromText IS NOT NULL
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
Comment Utility
Here's a way via query.  I broke it down into 6 calculated fields for clarity but its up to you if you want to combine them all into one.  The last field returns the email address and includes the LIKE operator to prevent a function error if no email address exists.

SELECT Field1, InStr([Field1],"@") AS [pos@], InStr([pos@],[Field1],".") AS posDot, InStr([posDot],[Field1]," ") AS posSpc, InStrRev([Field1]," ",[Pos@])+1 AS beg, IIf([posSpc]=0,Len([Field1])+1,[posSpc]) AS [end], IIf([Field1] Not Like "*@*.*","",Mid([Field1],[beg],[end]-[beg])) AS eMail
FROM TableName;

Open in new window

sqlRon
0
 

Author Closing Comment

by:LillyC
Comment Utility
Absolutely brilliant, thank you
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
The function method is so much easier, and you can add documentation to explain what you are trying to do.
0
 

Author Comment

by:LillyC
Comment Utility
Thank you Dale, the function method may be much easier but I'm not familiar with functions so I'm afraid it wouldn't be easier for me, but thank you anyway.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now