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

Michael Larimore
Michael Larimore used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Commented:
Select Left(email, instr(email, ".") -1) As FirstName, Mid(email, instr(email, "." +1) As LastName,
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015
Commented:
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

Distinguished Expert 2017
Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
so my response needs correction too ... just assumed you were your usual amazing self, Pat ~
Distinguished Expert 2017

Commented:
:)
I was distracted by a phone call and just quickly sent.  Sometimes work gets in the way.
Remote Training and Programming
Top Expert 2015
Commented:
actually,  I think it should be:
field --> LastName: Mid(email, instr(email, "." +1, instr(email, "@") -instr(email, ".") -1) As LastName

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
As you for sure have some addresses without a dot, this will be a safer method:

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

/gustav
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
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!
Michael LarimoreIndustrial Engineer

Author

Commented:
I still can't get the last name to work correctly.  Are you sure that is correct?
Michael LarimoreIndustrial Engineer

Author

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

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
what is the original string before @ and what do you get?
Michael LarimoreIndustrial Engineer

Author

Commented:
Thanks all!
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
you're welcome ~ happy to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial