X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Identifying A String Within A String, Access 2013    
post Nov 11 2019, 05:01 PM

Posts: 322
Joined: 15-December 16

Hi All,

This one is a bit complicated.

I have an inbox full of thousands of emails. each has a subject and within the subject may be a customer name or a code of some kind.

E.g. 'Subject: Contact me please - BobtheCustomer - Urgent!

I need to read each of these subjects, work out which customer the email belongs to and then assign the email to whoever manages that customer.

My Customer table contains thousands of customers against which is the managers name. I can match the customer to a customer table, but I would need to loop through the table and use the current record to test the subject string (e.g. Instr)

This would take a bajillion years to perform such looping - and I don't have a quantum computer. So would anybody have a suggestion about how I could approach this?

Go to the top of the page
post Nov 11 2019, 05:17 PM

Posts: 1,019
Joined: 25-January 16

First, you need code that will pull the subject line from each email (and/or body of email if you want to search there).

Then open a recordset of records that match criteria using InStr() OR use DLookup with InStr()

DLookup("Manager", "Customers", "InStr('" & strText & "',[CustomerName])>0")

Consistency is critical in string manipulation. If you cannot depend on subject always having customer name (or an account number) and always same spelling as in your table, you are probably SOL.

Request customers in order to speed response to include some consistent identifier such as an account number in subject line.

This post has been edited by June7: Nov 11 2019, 05:19 PM

Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
post Nov 11 2019, 05:31 PM

Posts: 35
Joined: 19-June 19
From: Tasmania, Australia

Does the email address it is from help to identify customers, or maybe even the domain of the email address? - If so that is going to make it easier.
or are these coming from an answering service?
Go to the top of the page
post Nov 11 2019, 06:17 PM

UtterAccess VIP
Posts: 3,677
Joined: 19-August 03
From: Auckland, Little Australia

Email address will be unique, but names generally arent unique. Thousands of customers, I would assume there are a few with the same names.

Beer, natures brain defragging tool.
Go to the top of the page
post Nov 11 2019, 07:47 PM

Posts: 1,999
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL

I haven't tried this from FMC tips, but found it via google. You didn't say explicitly that you're dealing with Outlook, so this may not be relevant.

You might consider regex for string matching/patterns.
This post has been edited by orange999: Nov 11 2019, 08:03 PM

Good luck with your project!
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    11th December 2019 - 07:41 PM