Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

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

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
Michael Larimore
Asked:
Michael Larimore
  • 6
  • 3
  • 3
  • +1
4 Solutions
 
PatHartmanCommented:
Select Left(email, instr(email, ".") -1) As FirstName, Mid(email, instr(email, "." +1) As LastName,
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
PatHartmanCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
so my response needs correction too ... just assumed you were your usual amazing self, Pat ~
0
 
PatHartmanCommented:
:)
I was distracted by a phone call and just quickly sent.  Sometimes work gets in the way.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
Gustav BrockCIOCommented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
Michael LarimoreIndustrial EngineerAuthor Commented:
I still can't get the last name to work correctly.  Are you sure that is correct?
0
 
Michael LarimoreIndustrial EngineerAuthor Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
what is the original string before @ and what do you get?
0
 
Michael LarimoreIndustrial EngineerAuthor Commented:
Thanks all!
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now