Solved

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

Posted on 2015-02-12
5
130 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

777 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