asked on

Query to filter out "free" email addresses

Hi Experts,

I want to create a query that will filter out "free" email addresses using domains such as hotmail.com.

Assume I have a Table called "Free Email Domain Names", with sample records like this:

strFreeDomainName
hotmail.com
yahoo.com
mail.com

Also assume I have a Table of email addresses, "Emails", with sample records

strEmail
bob@hotmail.com
jack@yahoo.com
real.person@somecompany.com

The query should return:

real.person@somecompany.com

Regards,
Leigh

Pratima

select strEmail from Email_address

where Substring(strEmail,CharIndex('@',strEmail)+1,Len(strEmail) not in (Select strFreeDomainName from Free_Email_Domain_Names)

LeighWardle

ASKER

Thanks pratima_mcs,

I am getting a syntax error:

Missing ), ], or Item in query expression 'Substring(strEmail,CharIndex('@',strEmail)+1,Len(strEmail) not in (Select
strFreeDomainName from Free_Email_Domain_Names)'.

Regards,
Leigh

Pratima

select strEmail from Email_address

where Substring(strEmail,CharIndex('@',strEmail)+1,Len(strEmail)) not in (Select strFreeDomainName from Free_Email_Domain_Names)

LeighWardle

ASKER

Thanks pratima_mcs,

Now I am getting this error:

Undefined function 'Substring' in expression.

I am running MS Access 2003.

Regards,
Leigh

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LeighWardle

ASKER

Thanks pratima_mcs,

Regards,
Leigh

Query to filter out &quot;free&quot; email addresses

Query to filter out "free" email addresses