INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

using a query to extract information from a text field

using a query to extract information from a text field

(OP)
I have linked my MS Access 2016 dbase to my Outlook and the table is working. I now want to extract some information from the content of the emails. An example of the email is below. How can I extract the customer name, number of tickets please? Its been a few years since I programmed and am rusty but I can remember doing this before from within a query. Any help appreciated!



You have received an order from John Doe. The order is as follows:


Order #18893 (June 23, 2017)

No of tickets 3 adults, 2 children

RE: using a query to extract information from a text field

There are a lot of string functions. Are the messages always in a common format? If so you can find the position of "order from" a pull everything up to the period. Look at instr, mid, left, right string functions.

RE: using a query to extract information from a text field

(OP)
Thanks so much. Yes the emails are always exactly the same format. The problem I can foresee with these string functions is that they seem to require me to specify the exact number of characters. I'm not sure how I can specify that when I don't know how long or short the customer name is. What I really need is to say "find 'order from'" then "tell me next 2 words after this". Not sure if that's possible but ill have a play around. thanks again for pointing me in right direction.

RE: using a query to extract information from a text field

The first function MajP suggested is Instr(). This will provide the start of one string within another string.

CODE --> debug

? instr("You have received an order from John Doe. The order is as follows:","order from")
 22 


You can also find the first period and do the math to return Mid().

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: using a query to extract information from a text field

It would also help if you would indicate which portions of your strings are constant, and which ones change.
For example, let's say all Blue parts change and they are the once you want to retrieve:

You have received an order from John Doe. The order is as follows:
Order #18893 (June 23, 2017)
No of tickets 3 adults, 2 children

You may also check Split() function...

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: using a query to extract information from a text field

CODE -->

Public Function GetName(EmailText As Variant) As String
  Dim lngStart As Long
  Dim lngEnd As Long
  If Not IsNull(EmailText) Then
    lngStart = InStr(EmailText, "Order from ")
    lngStart = lngStart + Len("order from ")
    lngEnd = InStr(lngStart, EmailText, ".")
    GetName = Mid(EmailText, lngStart, lngEnd - lngStart)
    GetName = Trim(GetName)
  End If
End Function 

Assuming the emails are exactly like you say and the only thing that changes is the name and the ticket counts, the above will return john doe
In a query you call the function and pass the email field name like
select field1, field2,...GetName([SomeField]) as OrderName

The Get number of tickets would be similar but not sure what you want. Do you want 5, or two fields 3 and 2, or the string "3 adults, 2 children".

RE: using a query to extract information from a text field

A regular expression solution:

CODE

Public Function getOrder(strText As String) As String()
    With CreateObject("vbscript.regexp")
        .Pattern = "(?:.*m )(.*)(?:\.[\s\S]*s )(.*)\r"
        getOrder = Split(.Replace(strText, "$1|$2"), "|")
    End With
End Function 

RE: using a query to extract information from a text field

Strongm,

Probably a very elegant and efficient solution but it's a bit presumptuous to think most people here could apply this without some additional assistance or links to resources. I would expect a solution that requires copying the code into a module and then using something like:

CODE --> expression

GetOrder("Order #18893 (June 23, 2017)") 

CODE --> expression

GetOrder("No of tickets 3 adults, 2 children") 

My ignorance is showing winky smile

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: using a query to extract information from a text field

>it's a bit presumptuous

Seriously? More presumptuous than providing an overview of instr without then explaining how that might be used to extract the relevant data?

Feed the whole email to it, and it returns an array with the two bit of info required. It is possible that the ticket info isn't quite in the format required, but given the example code that ought (given these are work forums for computer professionals) to be reasonably straightforward to figure out.

So ... assuming MailText contains the entire text we need to analyse as per the OP

CODE

Option Explicit

Private Sub CommandButton1_Click()
    Dim bitofinfo
    For Each bitofinfo In getOrder(MailText)
        MsgBox bitofinfo
    Next
End Sub

Public Function getOrder(strText As String) As String()
    With CreateObject("vbscript.regexp")
        .Pattern = "(?:.*m )(.*)(?:\.[\s\S]*ts )(.*)"
        getOrder = Split(.Replace(strText, "$1|$2"), "|")
    End With
End Function 

This is not production code BTW, just an illustration of what you can do.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close