Solved

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

Posted on 2015-02-12
5
132 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

738 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