Solved

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

Posted on 2016-09-09
13
47 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 35

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 35

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 19
ID: 41791819
so my response needs correction too ... just assumed you were your usual amazing self, Pat ~
0
 
LVL 35

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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…

821 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