Excel VBA Introduction Part 58.5 - SQL for Excel Files - Adding Criteria to a Query

This video explains how to add the Where clause to your SQL query to extract only the rows you want from an Excel file. You'll learn about the basics of adding criteria to the Where clause, including how to write basic logical tests and the range of available comparison operators. You'll also learn how to enter numbers, text and date values in your conditions, including the best date format to use. You'll learn how to combine conditions using the And and Or operators, and how to reverse the logic using the Not operator, including how to ensure multiple conditions are evaluated in the correct order by using brackets. You'll see how to use the special Like operator with wildcard characters for working with text columns. You'll also learn how to use the special In and Between operators to shorten longer criteria and make your SQL code more succinct. Finally, you'll learn how to use the Is Null and Is Not Null operators to find or ignore rows with missing values.

Chapters
00:00:00 Topic List
00:01:08 The Basic Setup
00:04:00 The Where Clause, Logical Tests and Comparison Operators
00:06:12 Writing a Simple Where Condition
00:07:14 Using Other Comparison Operators
00:10:32 A Note on Exclamation Operators
00:11:32 Writing Criteria without Column Headers
00:13:53 Comparing One Field with Another
00:15:20 Comparing Columns to Find Mistakes
00:16:17 Calculations in Criteria
00:20:20 Writing Text in Criteria
00:24:00 Text and Case Sensitivity
00:24:49 Text with Other Comparison Operators
00:27:51 The Like Operator
00:30:01 Some Basic Wildcard Patterns
00:32:00 Using Not Like
00:33:30 Writing Dates in Criteria
00:37:59 Using Different Date Formats
00:41:42 Dates with Other Comparison Operators
00:42:55 Dates with Time Values
00:47:50 Combining Conditions with the And Operator
00:52:00 Logical Tests and Round Brackets
00:52:50 Writing Multiple Conditions using Expressions
00:55:00 Combining Conditions with the Or Operator
00:57:09 Adding Multiple Conditions
00:58:26 Using Round Brackets to Group Conditions
01:01:00 Grouping Many Conditions
01:03:55 Using the In Operator with Numbers
01:06:19 Using the In Operator with Text
01:07:21 Using the In Operator with Dates
01:08:39 Using Not In
01:09:14 The Between Operator and Dates
01:12:10 Using Not Between
01:12:38 The Between Operator and Numbers
01:13:33 The Between Operator and Text
01:15:40 The Is Null and Is Not Null Operators
01:19:37 Null Text and Date Fields
01:20:59 What's Next?

[thwbn]

Feeling like Harry Potter: all the important and interesting news are send by a wise owl.

[karnabudhathoki]

WiseOwl; the most comprehensive learning Channel...

[Diegoad]

Man, please dont stop with your channel. Your deserve more subscribers! A very good content with so much information, thank you!

[frikduplessis]

Andrew this is really a blockbuster and must get Gold 👌 much better than Twilight 😀
Thank you for the detail around the date and time and can't wait for the follow-up, I was so frustrated with getting the NULL entry you will not believe thanks for the help,

[sabbirmoula]

Question Andrew, how efficient will it be to use this technique for large data set; like 44 col and 400k rows

[eiger]

Hi Andy, i don't want annoing you repeating again, you got huge knowledgees and a great way to teach them. Furthermore thank you share them with us.

This time i've 2 questions for you:
1 why Excel keep naming the new sheet you create with an increasing number after the word Sheet even if you delete them every time, it's because you don't close and reopen the app?
2 Question, could you write the macro in the Movies file, rename it Movies.xlsm and write back the query to another Workbook.xlsx creating new sheets i let you choose wicht parameter take, the Gendre ot the country? I'd asked you. this already ones, hope you'll find the time to share this idea. Thank you again for your time...

[youssefsedkey]

Question:

SELECT * FROM Products


WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"


ORDER BY ProductName;



how to handle (Apostrophe) in (Anton's)? ... Thanks advanced Andy.

[erikaahlander]

I do not really know if it is in this video, another one, or not covered.
But, I have connected my excel workbook to Teradata, I have put in the conntection and the SQL and everything runs smoothly.
But, I would like to add some criterias, e.g. date = cell in the excel document
Or, Name Like ANY Table column in Excel
Anything tips if I can find anything like this in one of your videos?

[calcetin]

Hello, thank you so much for your videos, they have been a big help.

I do have one question, if you have some time. I am working with workbooks that contain hidden rows. When using a SQL select statement, is there a way to bypass hidden rows from your ADO connected file?

I have been trying to research this online but I have not come across any way to do so. I am trying hard not to post process any data after the recordset has been copied over to the worksheet I am working on.

[nhatminh]

Hi ad
I have a problem that I want to be answered by the admin. let's say in cell d4 i have the value action. so in the where condition i can write where [genre] =
I tried but err.
Thanks you so much