Pick out the last word of text in Access query

[Nico]

I have a macro that automatically exports all e-mails in a folder to text,
and another that puts each item into a field in Excel, and a third that
imports them into a table in access. That all works great.

Now I need to match the last word in a text field with another table, for
example, the original Outlook subject line might read:

"RE: 1234 ABC/CBA/ABCD/DCBA Logon ID Request - John Smith"

I can match the sender and receiver, but I need to pick out "Smith" and
match it to an existing "Smith" field in an Access query.

Any ideas on how to pick out "Smith" from this? The number of characters
will vary, so I'm not sure how to go about it.

 

[Ken Sheridan]

You can parse the last word out of the string by using a combination of the
Mid and InstrRev functions, so in a query you could join the two tables like
so:

SELECT Table1.YourField, Table2.LastName
FROM Table1,Table2
WHERE Table2.LastName =
MID(Table1.YourField,INSTRREV(Table1.YourField," ")+1);

where Table1 is the table with the Outlook subject lines in a field
YourField and Table2 is the other table with the last names in a field
LastName.

Note that this will not work with names such as Victoria de los Ãngeles as
the expression would parse out 'Ãngeles' rather than 'de los Ãngeles' which
I'd imagine would be more likely to be the value in the Lastname field. So
lets hope you haven't emailed too many Spanish opera singers (or one–eared
Dutch painters as Vincent van Gogh would also be missed!).

Ken Sheridan
Stafford, England

 

[Nico]

Thanks Ken, that worked great!

You can parse the last word out of the string by using a combination of the
Mid and InstrRev functions, so in a query you could join the two tables like
so:

SELECT Table1.YourField, Table2.LastName
FROM Table1,Table2
WHERE Table2.LastName =
MID(Table1.YourField,INSTRREV(Table1.YourField," ")+1);

where Table1 is the table with the Outlook subject lines in a field
YourField and Table2 is the other table with the last names in a field
LastName.

Note that this will not work with names such as Victoria de los Ãngeles as
the expression would parse out 'Ãngeles' rather than 'de los Ãngeles' which
I'd imagine would be more likely to be the value in the Lastname field. So
lets hope you haven't emailed too many Spanish opera singers (or one–eared
Dutch painters as Vincent van Gogh would also be missed!).

Ken Sheridan
Stafford, England