Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Email criteria query only "gmail"

Copper Contributor

What is the criteria for a query if I only want to email my customers with a "gmail" type email? I've tried a hundred things, Like "gmail"  Like "*gmail"  Like "gmail.com" and every other variety including >= etc. Can anyone help me?

 

thanks

21 Replies
Everytime I run the query with all my various attempts and different * " Like >= etc they all contain yahoo, msn, hotmail, etc in the results. How do I get it to only show gmail?
I've tried Like"*"&[Enter character string]&"*" and then when I open the query it asks me for the character string, so I've tried gmail or @gmail or @gmail.com and none of them work, I get all kinds of other emails, yahoo, msn, etc etc

Please show us:

--Some sample data, not real email addresses, but samples that could be found in the table.

--The actual SQL from your query. Not a screenshot of the query design grid please. Copy and paste the SQL.

We might be able to identify the problem.

@George Hepworth what I'm trying to find is the query syntax to filter out only my friends who use Gmail. I have tried a bunch, "*.gmail.com" and every variety of that, does not work. The results always include some other users.

Is it possible to provide samples of the email addresses you are using?

Is it possible to copy and paste the SQL from your query into your reply?

"...does not work." is too generic to give us any idea what to suggest. Actual data to work with makes all the difference.
They are just normal email addresses like email address removed for privacy reasons or email address removed for privacy reasons. I created a query that singles out the states near Illinois and also singles out people with email addresses and now I want to add the criteria that I only get Gmail users in my query results and I have tried many different strings of criteria and none of them work properly
Please, I'm not kidding about the actual SQL. It would be very helpful.

@George Hepworth  I'm not sure why it automatically generates criteria in the state field that is a duplicate, I've tried to delete it, but it keeps coming back. Anyway, this returns results that are almost right but there are several other emails returned that are not gmail.com

 

SELECT DISTINCTROW [Fans with Email only Table].FNAME, [Fans with Email only Table].LNAME, [Fans with Email only Table].CITY, [Fans with Email only Table].STATE, [Fans with Email only Table].EMAIL
FROM [Fans with Email only Table]
WHERE ((([Fans with Email only Table].STATE) Like "il" Or ([Fans with Email only Table].STATE) Like "IL" Or ([Fans with Email only Table].STATE) Like "in" Or ([Fans with Email only Table].STATE) Like "IN" Or ([Fans with Email only Table].STATE) Like "mi" Or ([Fans with Email only Table].STATE) Like "MI" Or ([Fans with Email only Table].STATE) Like "wi" Or ([Fans with Email only Table].STATE) Like "WI" Or (([Fans with Email only Table].STATE) Like "il" Or ([Fans with Email only Table].STATE) Like "IL" Or ([Fans with Email only Table].STATE) Like "in" Or ([Fans with Email only Table].STATE) Like "IN" Or ([Fans with Email only Table].STATE) Like "mi" Or ([Fans with Email only Table].STATE) Like "MI" Or ([Fans with Email only Table].STATE) Like "wi" Or ([Fans with Email only Table].STATE) Like "WI")) AND (([Fans with Email only Table].EMAIL) Like "*.gmail.com")) OR ((([Fans with Email only Table].STATE) Like "il" Or ([Fans with Email only Table].STATE) Like "IL" Or ([Fans with Email only Table].STATE) Like "in" Or ([Fans with Email only Table].STATE) Like "IN" Or ([Fans with Email only Table].STATE) Like "mi" Or ([Fans with Email only Table].STATE) Like "MI" Or ([Fans with Email only Table].STATE) Like "wi" Or ([Fans with Email only Table].STATE) Like "WI") AND (("or") Like "zebra")) OR ((([Fans with Email only Table].STATE) Like "il" Or ([Fans with Email only Table].STATE) Like "IL" Or ([Fans with Email only Table].STATE) Like "in" Or ([Fans with Email only Table].STATE) Like "IN" Or ([Fans with Email only Table].STATE) Like "mi" Or ([Fans with Email only Table].STATE) Like "MI" Or ([Fans with Email only Table].STATE) Like "wi" Or ([Fans with Email only Table].STATE) Like "WI") AND (([Fans with Email only Table].NOTE) Like "Zebra"));
Thank you. That's a gnarly SQL statement and it will, no doubt, take a while to sort it out -- especially with no sample data to test against.

I will say that I see a lot of "OR" concatenators and that's most likely going to turn out to be where the problem lies.

Without this SQL, though, it would have been 1,000 to 1 shot that anyone could have offered any useful suggestions. :)
What is the purpose of that last criteria about Zebras? And why did you use LIKE in all of the criteria where the only possible values are the two letter states?

Understanding the reasoning might help point to a more viable approach....
I write the word "zebra" or "Zebra" in the note field for friends who enjoy going to a piano bar named Zebra. And I just want all friends in IL and neighboring states so I was trying to write a criteria that would filter out only those friends.

@DougSaleeby 

 

I think this might be what you really want.

 

SELECT DISTINCTROW [fans with email only table].fname,
                   [fans with email only table].lname,
                   [fans with email only table].city,
                   [fans with email only table].state,
                   [fans with email only table].email
FROM   [fans with email only table]
WHERE  ( ( ( [fans with email only table].[state] ) IN
           ( "il", "in", "mi", "wi" ) )
         AND ( ( [fans with email only table].[email] ) LIKE "*.gmail.com" )
         AND ( ( [fans with email only table].[note] ) LIKE "*zebra*" ) ); 

That returns zero results. Should I capitalize the states?

That means no records meet those criteria. It should not be case sensitive.

I apologize for not catching this in the initial SQL.

AND ( ( [fans with email only table].[email] ) LIKE "*.gmail.com" )

There is an extra period between the asterisk and gmail. That should be an ampersand, not a period, should it not?
AND ( ( [fans with email only table].[email] ) LIKE "*@mail.com" )

Thanks. I have tried it every way, with a period, without, with asterisk before, after, both, it just will never work. Thanks for your help. I think I'll just create a "yes/no" field and click "yes" for all the gmail users. This problem is all being cause because of 550-5.7.26 authenticate with at least one of SPF or DKIM. I'm sending out 186 emails as a mass thing, as I've done for years, and now all of a sudden I'm getting this so I'm trying to figure out a solution and then send to the gmails who never got the original. I've had my webhost try and try but that's not helping, so I'll keep working on the source of the problem
I have 113 gmail users on my list. I just removed the period before gmail and it then returned 142 with many being yahoo, msn, hotmail, etc. So then I removed the * and put the period back in, that returned 118 so less yahoos and msns and hotmails, but still not the correct results.
Did you REPLACE that period with the appropriate @ like this:

AND ( ( [fans with email only table].[email] ) LIKE "*@mail.com" )
I have Like "*@gmail.com", returns 142 names

And did you try it with ONLY that one criteria, i.e. without the state filter and the note filter?

that would be

 

WHERE [fans with email only table].[email] ) LIKE "*@mail.com" 


As previously, noted, I can't test without sample data.

1 best response

Accepted Solutions
best response confirmed by DougSaleeby (Copper Contributor)
Solution

@DougSaleeby 

This is how trouble-shooting works.


Try one thing first, isolate the individual parts--in this case the criteria for email.

Once you verify that it works as required, the next step is to add the second criteria, either the IN() clause for states or the word in the Notes field. 

Once you verify the first two work as required, you can add the third and test again.

 

I've repeatedly stated I can't test for you without adequate sample data. I can suggest SQL Syntax to use, but it's on you to verify it.

 

There should be only ANDs between the three criteria, because you want all three to apply:


In specific States AND with specific email hosts AND with a specific value in the Notes Field. 

View solution in original post