Solved

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

Posted on 2015-02-12
5
131 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)
ID: 40605874
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
ID: 40605961
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
ID: 40609905
Absolutely brilliant, thank you
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40610124
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
ID: 40611088
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 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