By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,147 Members | 744 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,147 IT Pros & Developers. It's quick & easy.

Parse first name space last name from email address containing first dot last name

MitchR
P: 63
Howdy folks,
I am living the Access Dream as always but, I am in need of some helping hands here. I am looking to find a way to obtain First name space Last name from an email address of firstname.Lastname@Domain.com. My table Status_and_Updates has a column of Email_ADDR and I want to parse out the Firstname space lastname out of the email address. I can parse out the first name with a left function and I have tried and tried Right and Mid functions to parse out the lastname with no success I am affraid
Expand|Select|Wrap|Line Numbers
  1. Left([Status_and_Updates].[Email_ADDR],InStr([Status_and_Updates].[Email_ADDR],".")-1)+" "+Mid Function
I have come very close but I am not able to complete the function to parse just the last name from between the dot and the "@."
Aug 23 '18 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Hi Mitch.

You don't indicate whether this is in a query or in VBA. I'll start with a Public Function and you can say whether that works for you or whether the question needs to be amended :
Expand|Select|Wrap|Line Numbers
  1. Public Function GetNameFromSMTP(ByVal strSMTP As String) As String
  2.     strSMTP = Split(strSMTP, "@")(0)
  3.     GetNameFromSMTP = Split(strSMTP, ".")(0) & " " & Split(strSMTP, ".")(1)
  4. End Function
Aug 23 '18 #2

MitchR
P: 63
Hi NeoPa,
Thank you so much in helping me with a solution. I am so sorry, I was just a little frustrated at the time I wrote my post. The answer to the question is that my formula is from a Query. I was looking for a way to parse the data from within the query itself.
Aug 23 '18 #3

NeoPa
Expert Mod 15k+
P: 31,494
Well, as you'll probably have guessed, that's a lot more complicated - not to say awkward.
Expand|Select|Wrap|Line Numbers
  1. Left([Status_and_Updates].[Email_ADDR],InStr([Status_and_Updates].[Email_ADDR],'.')-1)+' '+Mid([Status_and_Updates].[Email_ADDR],InStr([Status_and_Updates].[Email_ADDR],'.')+1,InStr([Status_and_Updates].[Email_ADDR],'@')-InStr([Status_and_Updates].[Email_ADDR],'.')-1)
Or, made a little more readable in order to understand, we'll replace [Status_and_Updates].[Email_ADDR] with [X] :
Expand|Select|Wrap|Line Numbers
  1. Left([X],InStr([X],'.')-1)+' '+Mid([X],InStr([X],'.')+1,InStr([X],'@')-InStr([X],'.')-1)
PS. This means you can copy/paste the latter one then simply do a Find & Replace to turn it into what you need specifically.
Aug 24 '18 #4

Nauticalgent
P: 92
Pardon me for jumping in here, but I thought this might be useful to others.

Why Team Access did not make the Split() Function available in the Query grid is beyond me, but thanks to Daniel Pineault, there is a pretty neat alternative:
<Link removed - feel free to search for it>
Aug 24 '18 #5

MitchR
P: 63
Team,

Thank you so much for everyone helping me!

NeoPa, Your solution was absolutely on point. I cannot say thank you enough. As awkward as the query solution can be, it is absolute gold for my process on this app.

I really appreciate everyone's assistance and patience on this one.

Thank you again!

P.S. Indenting my code has been a tremendous help! Thank you!
Aug 24 '18 #6

NeoPa
Expert Mod 15k+
P: 31,494
MitchR:
P.S. Indenting my code has been a tremendous help! Thank you!
Very pleased to hear it Mitch. It's a very important point.

Also pleased to hear that helped. As our NauticalGent has pointed out you can also call code in your project as long as it's declared as Public. That's why my first response could also have helped in this situation had you chosen to go that route.
Aug 24 '18 #7

NeoPa
Expert Mod 15k+
P: 31,494
NauticalGent:
Pardon me for jumping in here, but I thought this might be useful to others.
No problem with that my friend.

Unfortunately, particularly as Daniel is a personal friend and fellow Access MVP, I was obliged to remove the link due to the site regulations on linking to other sites that are commercial and not sites provided by the provider of the software itself.

Nevertheless, the point is made. You can set up a wrapper function in your own project as long as it's defined as Public. BTW it's easier to use from a standard module but not absolutely necessary.

In this case though, unless you want a project that uses a variation of Split() from multiple different QueryDefs or other SQL, it makes better sense to provide a function to do the job itself. An example of that is in post #2.

NauticalGent:
Why Team Access did not make the Split() Function available in the Query grid is beyond me
QueryDefs, just like any part of access using SQL or expressions, is clearly not in the VBA environment. Jet/ACE SQL, which runs the SQL in an Access database, has access to The Expression Service (TES). TES has access to one/some of the most basic libraries that Access has, including your own project, but certainly not the VBA one. Split() is found as VBA.Strings.Split() - thus is unavailable to TES even though it's available to your projects VBA code. I hope that explains why MS never made Split() available to use in expressions - even though most of us would love to have it available ;-)
Aug 24 '18 #8

Post your reply

Sign in to post your reply or Sign up for a free account.