|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Null Math By Richard Rost 
3 years ago
Use Null Math Along With String Concatenation In today's video, I'm going to show you how to use Null addition to handle missing values in your string concatenation. Links
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, null math, null addition, concatenation, combining names
TranscriptWelcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we are going to learn about Null Math. What is Null Math? It is something where, when you add anything to Null, it becomes Null. You can use this to your advantage when doing string concatenation and putting different strings together. If you do not know what concatenation is, it is putting first name and last name together, for example, in a query field or in a form field. Go watch my concatenation video. It is free. It is on my website. It is on my YouTube channel. Also, go watch my calculated fields video. Now that you are familiar with concatenation, you can use it in a query to put together, for example, first name and last name in a greeting line: "Dear", first name, a space, last name, and then a comma. If both first name and last name have values in them, you get "Dear Richard Rost,". Let's say last name is Null. What happens is you get "Dear Lauren ", and then a space, and then the Null value because that space goes there. So you get this awkward looking space before your comma. What we do with Null Math is put it in parentheses. We take last name and then we add it to that space character, and what happens is anything plus Null equals Null. So if last name is Null, that space becomes a Null. This whole thing evaluates to Null. You will get "Dear Richard", and then if Rost is there, you will get "Richard Rost" with the space and then the comma like that. But if Lauren does not have a last name, that space goes away because you are adding it to Null. Same thing if you are adding first name and spouse and last name. With concatenation, you will get "Joe and Sue Smith." But let's say spouse is Null. That "and" is going in there either way with concatenation, so you get "Joe and Smith." Looks really awkward. If you know how to use Null Math, you take that "and" and you add it to spouse. That way, if spouse is Null, this whole thing evaluates to Null. Then you get first name and space, last name. So you get "Joe and Sue Smith" or just "Joe Smith". There are other ways to do this too, with a whole bunch of if statements and all that, but this is really easy, isn't it? Here I am in a query that I put together. I have first name, last name, and spouse - those are my fields. Here are the different greetings right there. "Dear Joe Smith." This is with concatenation. This is with Null Math. You can see here, for example, with Diana. She has a last name, so you get "Dear Diana ," with a space before the comma. With the Null Math, that space goes away. Here is concatenation with the spouse. Diana is missing a spouse. Over here, with Null Math, it goes away. If I take a look at it in design view, here is the one with the concatenation. I will zoom in so you can see it. Greeting one: "Dear" first name, a space, and last name. Here is greeting two, and there it is with Null Math. Same, just like I had in the slide. Spouse one with concatenation, and the good one with Null Math right there. That is Null Math. I have a more complicated video for combining names together if you get spouses with different last names. It takes a couple of nested if functions. You can do it, but it is a little more complicated. Again, this is a free video. It is on my website. It is on my YouTube channel. I will put a link down below. You can just click on it and go watch this video too. This one will show you if you have James Kirk and Carol Marcus, it puts something out like that. That is it for Null Math. If you liked this video, give me a like and a share. Make sure you subscribe to my channel. If you want to learn more Access and you have not checked out my four hour Access Beginner Level 1 class, it is absolutely free. There is a link right there. I will put one down below you can click on. Thanks for watching and we will see you next time. QuizQ1. What is the main concept of Null Math as described in the video?A. Multiplying Null by a value returns the value B. Adding anything to Null results in Null C. Null values are automatically converted to zero D. Null values are ignored in all calculations Q2. What does concatenation refer to in the context of the video? A. Formatting numbers with commas B. Summing numerical fields together C. Putting strings like first name and last name together D. Adding a Null value to another field Q3. What happens when you concatenate first name and last name and the last name is Null? A. You get an error B. The first name disappears C. There may be an awkward space before the comma D. The entire output becomes Null Q4. How does using Null Math help with string concatenation when optional fields may be Null? A. It removes all spaces between words B. It ensures only numbers are included C. It makes sure unwanted spaces or words do not appear if a value is Null D. It turns all Nulls into zeros Q5. In the example "Joe and Sue Smith," what problem does Null Math solve when the spouse field is Null? A. It adds "and" even if the spouse is missing B. It deletes the entire name C. It hides the "and" if the spouse is Null D. It combines last names into one Q6. According to the video, what is an alternative way to handle Null values in concatenations besides using Null Math? A. Using join tables B. Writing multiple If statements C. Formatting output as currency D. Importing data from Excel Q7. What is the primary benefit of using Null Math in Access queries? A. It makes all fields mandatory B. It simplifies handling optional fields without extra code C. It automatically fills Nulls with default values D. It sorts records alphabetically Q8. Which Access objects were discussed as places where you might use Null Math? A. Tables only B. Queries and forms C. Reports exclusively D. Macros and modules Answers: 1-B; 2-C; 3-C; 4-C; 5-C; 6-B; 7-B; 8-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on the concept of Null Math in Microsoft Access. Null Math refers to how adding anything to a Null value results in a Null. This concept is especially useful when you're working with string concatenation, for example when you want to combine first name and last name fields together in a query or form.For those unfamiliar with the term, concatenation is simply joining two or more strings together, such as putting a first name and last name into a single greeting. If you'd like a more detailed introduction to concatenation, I recommend checking out my free video on that topic available on my website and YouTube channel. Be sure to also watch my tutorial on calculated fields if you haven't already. Let's explore how Null Math can help in a practical scenario. Imagine you're creating a greeting that combines "Dear", a first name, a space, a last name, and finally a comma. If both first name and last name are filled, the result will be something like "Dear Richard Rost,". However, if the last name is Null, you end up with a space before the comma, like "Dear Lauren ,", which looks awkward. To handle this, Null Math comes into play. By placing the last name and the space inside parentheses and adding them, if the last name is Null, the whole expression evaluates to Null. As a result, the unnecessary space before the comma is eliminated. When there's a last name present, you'll get the full "Dear FirstName LastName," but if only the first name is available, the space doesn't appear where the last name would be. This concept also applies when combining a first name, optional spouse name, and last name. For example, you might want to display "Joe and Sue Smith" if both names are present. If only Joe's name is available and the spouse's name is Null, traditional concatenation would result in "Joe and Smith," which includes an awkward "and." Using Null Math, you would combine the word "and" directly with the spouse's name. If the spouse field is Null, then "and" and the space disappear, leaving you with just "Joe Smith" as intended. Of course, there are more complicated methods such as using multiple if statements to check each possible combination, but applying Null Math is a much simpler and more elegant solution. To demonstrate, I created a query with fields for first name, last name, and spouse, alongside various greeting formats. Comparing standard concatenation with Null Math, you can see the difference: for example, if Diana does not have a last name, concatenation produces "Dear Diana ,", but Null Math correctly removes the extra space. The same pattern holds for greetings involving a spouse – if that field is missing, Null Math ensures the result reads naturally. In the design view of the query, you'll notice two fields: one using simple concatenation, and another employing Null Math. The difference in the results is immediately clear. For more advanced name combinations, such as situations where spouses have different last names, there's a more in-depth video available. It covers nested if statements and more complex logic, although it is a bit more advanced. Remember, this video is free to watch on my website or YouTube channel. There is also a free four-hour Access Beginner Level 1 course available, which you can access via the link provided. For step-by-step instructions on everything I covered here, you can find a complete video tutorial on my website at the link below. Live long and prosper, my friends. Topic ListUnderstanding Null Math in Microsoft AccessHow concatenation handles Null values Using Null Math to eliminate unwanted spaces Applying Null Math to fields with missing data Combining name fields with Null Math in queries Improving greeting lines with Null Math Comparing traditional concatenation to Null Math Viewing and editing query fields using Null Math Eliminating awkward formatting in concatenated strings |
||||||||||||||||||||
|
| |||
| Keywords: FastTips Access null math, null addition, concatenation, combining names PermaLink Null Math in Microsoft Access |
