Solved

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

Posted on 2016-09-09
13
43 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
Comment Utility
Select Left(email, instr(email, ".") -1) As FirstName, Mid(email, instr(email, "." +1) As LastName,
0
 
LVL 18

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
Comment Utility
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
Comment Utility
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 18
Comment Utility
so my response needs correction too ... just assumed you were your usual amazing self, Pat ~
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
:)
I was distracted by a phone call and just quickly sent.  Sometimes work gets in the way.
0
 
LVL 18

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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 18
Comment Utility
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
Comment Utility
I still can't get the last name to work correctly.  Are you sure that is correct?
0
 

Author Comment

by:Michael Larimore
Comment Utility
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 18
Comment Utility
what is the original string before @ and what do you get?
0
 

Author Closing Comment

by:Michael Larimore
Comment Utility
Thanks all!
0
 
LVL 18
Comment Utility
you're welcome ~ happy to help
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

13 Experts available now in Live!

Get 1:1 Help Now