Solved

How can I split a string into 2 parts in a Microsoft Access Query

Posted on 2016-09-09
13
46 Views
Last Modified: 2016-09-09
I have a field that is an email address.  This address always follows the format of "john.smith@gmail.com".  The names are not always the same length.  I would like the output to read "john smith".  Any help with this would be appreciated!
0
Comment
Question by:Michael Larimore
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41791787
Select Left(email, instr(email, ".") -1) As FirstName, Mid(email, instr(email, "." +1) As LastName,
0
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41791804
adding on to Pat's comment ...

if you are putting this formula into a cell on the design grid (as opposed to editing the SQL string):
field --> FirstName: Left(email, instr(email, ".") -1) 
and 
field --> LastName: Mid(email, instr(email, "." +1) 

Open in new window

0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41791805
Whoops - hit send too soon

Select Left(email, instr(email, ".") -1) As FirstName, Mid(email, instr(email, "." +1, instr(email, "@") -1) As LastName,

Forgot to "end" the last name at the @ sign.
0
 
LVL 19
ID: 41791819
so my response needs correction too ... just assumed you were your usual amazing self, Pat ~
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41791827
:)
I was distracted by a phone call and just quickly sent.  Sometimes work gets in the way.
0
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41791841
actually,  I think it should be:
field --> LastName: Mid(email, instr(email, "." +1, instr(email, "@") -instr(email, ".") -1) As LastName

Open in new window

0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41791843
As you for sure have some addresses without a dot, this will be a safer method:

    EmailName: Replace(Left([emailaddress], InStr([emailaddress],"@")-1),"."," ")

/gustav
0
 
LVL 19
ID: 41791855
good point, gustav ...  your equation should be TRIMmed too -- not that it really matters though

I so much prefer writing functions since they are more clear, especially when equations get long!
0
 

Author Comment

by:Michael Larimore
ID: 41791883
I still can't get the last name to work correctly.  Are you sure that is correct?
0
 

Author Comment

by:Michael Larimore
ID: 41791887
Actually got it!  There was a comma missing...Here was the final code:
Last: Mid([supervisor1email],InStr([supervisor1email],".")+1,InStr([supervisor1email],"@")-InStr([supervisor1email],".")-1)

Open in new window

0
 
LVL 19
ID: 41791889
what is the original string before @ and what do you get?
0
 

Author Closing Comment

by:Michael Larimore
ID: 41791891
Thanks all!
0
 
LVL 19
ID: 41791893
you're welcome ~ happy to help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now