Excel VBA Introduction Part 58.9 - SQL for Excel Files - Nulls in Expressions

In this video you'll learn how to work with nulls in your SQL queries. You'll see how to use the IS NULL expression and IsNull function to check for the existence of nulls and how to use the IIf and Switch functions to replace nulls with other values. You'll also see what happens when a null is involved in a calculation and how to make sure that the calculation returns a non-null result.

Chapters
00:00 Topic List
00:42 The Basic Setup
02:34 Testing for Nulls in the WHERE Clause
04:24 Using the IIf Function to Replace Nulls
07:30 Using the IsNull Function
09:23 Replacing Nulls with Different Data Types
11:55 Nulls in Calculations
14:58 Changing the Example Query
16:15 Concatenating Names
17:11 Checking for Null in Text Columns
20:01 Testing for Nulls with the Switch Function
24:49 Calculating the Difference Between Dates
28:05 Replacing Dates using IsNull
30:13 What's Next?

[rogerirvine]

This whole series of SQL for Excel has been VERY useful. Thanks again.

[imranbhatti]

Great Andrew looks like another nice video. I am tracking this series and will be watching this after I get some of my pending tasks completed. I have 4 to 5 videos of this series to Watch Later.

[frikduplessis]

Awesome Andrew Thank you really brilliant 👌👌👏👏👏 I'm really interested in the date time calculations, the way I did it was by bringing in the data with SELECT statement and then hammered 🔨 everything in place with VB code 😀 however with you help I'm doing things differently

[aNDy-qhem]

Hello Andrew, I just wonder if COALESCE function that replaces null works also in Sql for Excel.

[juanpablohorn]

Hey Andrew! I hope you're doing well. I have a suggestion that depending on your response might turn into a question. When you create calculated columns for the Budget, you said that the default format for those lines is text, therefore you cannon make calculations on them further on. My suggestion is, is it possible to multiply the budget by 1 within your query so the value at the end gets converted to number format? I know this is true when working on excel, but I'm not sure if it works for Activex.

As always, thanks a lot for the great content!