Access 365 query to return only domain info from Emailaddress

mrosier
mrosier used Ask the Experts™
Hello! I have an Access 2016 (via O365) table that is just an email address. What I am trying to do seems like it would be simple, but I am having trouble finding an answer that isn't sql server. I want to query all the results to return just the domain. So if I have a bunch of rows of myemailaddress@mydomain.com (different addresses of course) how can I go about just returning a list of mydomain.com? I have a workaround where I import that field from a flat file to a new table and use the @ symbol as a field delimiter, but I am hoping for a way to query instead. Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018
You should be able to use an expression such as
Domain: Right([Email], Len([Email])-InStr([Email], "@"))

Open in new window

or
Domain: Mid([Email], InStr([Email], "@")+1)

Open in new window


Where [Email] is the name of the table field housing the email address to parse.

Author

Commented:
Well I tried something like this I saw off a google search and it gave me an error. Specifically let's say my table is called TableA and the field name for email is EmailAddress. Can you help me with exact syntax?
President / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Try
SELECT Mid([EmailAddress], InStr([EmailAddress], "@")+1) AS Domain 
FROM TableA; 

Open in new window

Author

Commented:
That's the one thanks very much!
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018
My pleasure.

When it comes to parsing strings, the following are your best friend:

  • Left
  • Right
  • Mid
  • InStr
  • InStrRev
  • Len

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