One of Outlook's little known gems is the SQL filter in the Views Filter dialog. Although it looks confusing and technical, it's surprisingly easy to use. In many cases, you can let Outlook create a query for you by creating your criteria on the other tabs then enable SQL filtering and edit the query as needed. While you can create power filters if you know some SQL syntax, you don't need to know a lot about SQL to create filters not possible using the other filter tabs.
If you use the filter dialog often, you've already discovered the filters use the AND operator to string different criteria together. For example, you can create filters to view messages "From Amy AND Received in the last 7 days ", but not filters for messages "From Amy OR Received in the last 7 days". The filter dialog doesn't support the NOT operator directly, although you can use the Advanced filter to create some NOT filters indirectly. Using the SQL filter, you can easily create OR and NOT filters.
When you create the filter "From Amy AND Received in the last 7 days", the SQL looks like this:
("urn:schemas:httpmail:fromname" LIKE '%Amy%' AND %last7days("urn:schemas:httpmail:datereceived")%)Switch to the SQL tab and change the AND to OR, so the rule shows all e-mail from Amy OR all e-mail that was received in the last 7 days.
("urn:schemas:httpmail:fromname" LIKE '%Amy%' OR %last7days("urn:schemas:httpmail:datereceived")%)You can also use the NOT operator to restrict views. This filter shows all messages not from Amy that were received before yesterday.
NOT ("urn:schemas:httpmail:fromname" LIKE '%Amy%' OR NOT "urn:schemas:httpmail:datereceived" <= 'yesterday')More Information
See Using Query Builder another option to use AND / OR operators in the filter dialog.
Matt says
Can you get this view to appear on the Search Folders display?
Diane Poremsky says
Search folders don't have a SQL tab so it can't be used in the search... but it does work in the view for a search folder.
If you want to use SQL to create the search criteria, you can use VBA and redemption. (I need to write that up.)
James says
Would be helpful to know how to get the SQL tab to display if it's not there. I had it before, on another work laptop, and I remember having to configure that. Now, I have to find it again, and no one seems to be saying how to get it there but assuming it is there. Still looking...
Diane Poremsky says
The SQL tab is there if it is supported but not all filter dialogs support it. You should have it on Views but not with conditional formatting.
Daniel says
Am I right in understanding there's just no way to get this to show on the Advanced Find dialogue?
Diane Poremsky says
Correct. It's not available on all filter dialogs and there is not a registry key to add it. You can add the query builder, which gives control over AMD/OR operations.
https://www.slipstick.com/outlook/using-query-builder/
Daniel says
Thanks for confirming DIane. I've added the QB already.