473,557 Members | 2,776 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

MitchR
65 New Member
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.Lastn ame@Domain.com. My table Status_and_Upda tes 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
7 1989
NeoPa
32,564 Recognized Expert Moderator MVP
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
65 New Member
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
32,564 Recognized Expert Moderator MVP
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_Upda tes].[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
100 New Member
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
65 New Member
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
32,564 Recognized Expert Moderator MVP
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
32,564 Recognized Expert Moderator MVP
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.Spl it() - 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

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

Similar topics

1
29159
by: Prasad Karunakaran | last post by:
I am using the C# DirectoryEntry class to retrieve the Properties of an user object in the Active Directory. I need to get the First Name and Last Name as properties. I know it is not supported with the ADSI NT Provider and only supported in the LDAP Provider. So given an UserId (UID) how can I read the First Name and Last Name using LDAP...
3
11112
by: Edmund Wong | last post by:
I have one client fiield that stores First and last name. I would like to separate the names into a lastname field and firstname field. Can anyone tell me how to do this? thanks
1
5045
by: Prasad Karunakaran | last post by:
I am using the C# DirectoryEntry class to retrieve the Properties of an user object in the Active Directory. I need to get the First Name and Last Name as properties. I know it is not supported with the ADSI NT Provider and only supported in the LDAP Provider. So given an UserId (UID) how can I read the First Name and Last Name using LDAP...
0
1679
by: Peter | last post by:
Using CDO 1.21 how do I get First Name and Last Name from Global Address Book? I have the following code where I retrieve LastName and FirstName in one field, but how do I get LastName and FirstName and middle initial in separate fields? string keyName = "Software\\Microsoft\\Windows NT\\CurrentVersion\\" + "Windows Messaging...
0
1459
by: preeti13 | last post by:
Hi friends i am here again with my probelm. i have one login page when first login on the page it is checking the active directory user exist or not if user exist then go the next page all i nee to know how i can do when the user login it take you the next page i want to display user first anem in the txtbox and last name in the other text box so...
1
5955
by: hbkiwi | last post by:
Hi, Just playing with Access fairly new to it (like days old). Also it's my first post here so if I'm in the wrong place, sorry.. What I'm wanting to know is basically I have lets call it Table Data and I've got Last Name, First Name in the columns. I than have Table Full Name, with column Full Name. I have a relationship between the primary...
3
2379
by: Mo Ewing | last post by:
I have a field in my database with people's names. Each field has only one first name but may have more than one last name. The first name is always the first word in the field. I would like to split this field into two fields, first name and last name. Can I construct an action query to do that for me?
12
4078
by: mcupito | last post by:
I have a text box where users will enter a first and last name, and then search by those parameters. I can find plenty of examples and have done search functions where first name and last name are separate text boxes, however I am not sure how to go about this. I understand it would be some kind of concatenation, yet I'm not sure how to go...
6
1170
MitchR
by: MitchR | last post by:
I need some help folks. I am trying to extract the first name space last name from an email address containing first name dot last name. I was able to complete a work around to extract first name. Then I am to extract the last name but not "subtract" the "@Domain.com from last name portion of the string. I know there is a way to perform this...
0
7626
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8061
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7591
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7911
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5454
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2044
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1161
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
867
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.