|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
NZ Function By Richard Rost 
6 years ago
Use the NZ Function to Convert NULL to Zero In this Microsoft Access tutorial, I'll show you how to use the NZ function to change NULL values into ZERO, or any other value that you want. Bill asks: "In my form, I have to add two values together to get a total time an employee worked on a project. I have the default value set to zero, but if they somehow delete the value it messes up my calculations. Is there any way to stop this?" MembersI'll show you how to set the default country to "United States" if the customer doesn’t have a country specified. If the customer doesn’t have a start date, use NZ to set it equal to today so they don’t get a gift card reserved for long-time customers. If a customer doesn’t have any orders in the database, use a LEFT JOIN to show them in a query and then NZ to set their order total to $0. Check to see if a customer's email address exists in the database. If so, warn before adding. Use NZ to make DLookup not error out. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! Links
IntroIn this video, you will learn how to use the NZ function in Microsoft Access to handle null values by substituting them with zero or another default value. I will show you how null values can affect your calculations, demonstrate how to build a query that uses the NZ function to make sure sums work even when fields are empty, and discuss situations where making fields required is or isn't a good idea. You will also see practical examples of how NZ can help when entering or importing data where some values might be missing.TranscriptWelcome to another TechHelp video brought to you by accesslearningzone.com. I am your instructor, Richard Rost.In today's lesson, I'm going to teach you how to use the NZ function. That's a function that allows you to change null values into zero or any other default value that you want. Today's question comes from Bill. Bill says, "In my form, I have to add two values together to get a total time an employee worked on a project. I have the default value set to zero, but if they somehow delete the value and mess up my calculations, is there any way to stop this?" Well, Bill, there are a couple of different things you could do. First, you could make the value required. It's a property in your table settings. You can go in and set it to required. If I look at my customer table, for example, and pick a value like I've got family size here, the default value is set to zero, and you can make this required. If you set that to required, they have to put a value in there. They can't just blank over it. They can't delete and erase the value. Personally, I use this very sparingly though. I don't like to use required for a number of different reasons. First of all, a lot of people like to leave a value blank if they don't know what the answer is. For example, the number of children. If you're not sure how many children they have, you might not want to put a zero in there because that says I know they have zero children. So sometimes a null value is good in indicating that I have no idea what the answer is. Like your hours worked, for example. They might not have done that particular one yet. I'm not sure exactly what your values are, but sometimes a zero isn't the right thing to store in the table. Now, moving beyond that, there is a way that you can assume the value is zero if it's null. That's what the NZ function is for. So let's real quick just set up another table here. Double design, an ID field, and then I've got a value one, which is a number, and a value two, which is a number. Very simple. Save this as my value T, VALT. Primary key defined, sure. Close it down, open it back up again. Now my default value is zero, like you have. So let's say we've got zero and eight, and then the next two values here: three and nine, one and eight. The next record he hits delete for some reason, and then puts an eight in here. And then we'll put a couple more values in. And again, here he deletes that one. So there are my records. Ignore the ID pretty much, but value one, value two. Now, in a query, I can add these together. So let's close this, create a query, create query design, or this works the same way in forms - you can do it in a form too. Bring in my VALT. Bring in value one, value two. See what it looks like at the run button. Let's add them together over here. So this is going to be VALTotal: VAL1 plus VAL2. That's how you make a calculated field in a query. I've got lessons for that. I'll put a link in the description below the video. Let's run it and see what we get. Zero and eight is eight. Three and nine is twelve. What happens down here? Null plus eight is null. If either one of those values is null, the result is null, which doesn't help if you're trying to add up hours. So the NZ function can say if this is null, assume it is whatever. If you want it to assume it's zero, you put a zero on the function. Here's how this works. Go to design view. Down here, we're going to wrap these guys inside of NZ. NZ, open parenthesis. Now after VAL1, you don't have to do this, but I like to specify the zero because it can change based on the data type that you're working with. And then over here for VAL2, NZ, like that. Just like that. So it's NZ(VAL1, 0) plus NZ(VAL2, 0). That says if VAL1 is null, make it zero, plus if VAL2 is null, make it zero. You can't wrap the whole thing because then it's... Well, you can actually... No, you probably could wrap the whole thing in NZ. Let's see if this one works first. There. Now it works. See, it assumes that it's zero if it's null. I'm just curious; now I want to see. You probably could wrap the whole thing in NZ: NZ(VAL1 plus VAL2). Actually, that's not the result that we want. Because you're going to get zero. So you want the first one that I had. Let's go back here. That's what you want. Because you're saying, basically, if you wrap the whole thing in NZ, if the result is null, give me a zero. So null plus eight would be zero. Maybe you want that. Maybe if either VAL2 is in there, give it a zero. It all depends on what you want. In this particular case, I want to assume that if a single value is null, make it zero, and that's what you want to add together. Now you can use this calculation in all of your reports and stuff without forcing the user to have to put values in here. It's also handy if you're using a table that might have old records in it. If you've got thousands of records and some of the old ones maybe don't have values, you could assume they're zeroes. Yes, you could run an update query or something and change them all. But again, what if you're not sure what those values were? Maybe they were left null on purpose. Same problem with that required field. Same problem if you're importing data. If you're pulling in data that someone else gave you from a spreadsheet or whatever, there might be some null values in it. If you do an import, sometimes if you specify required and you're importing data, and there is no data in that field, Access won't import it. It won't add that record. So you'll lose data if you have your required fields set. And that's another nightmare. So that's why I generally let people put whatever they can into the tables. Then I fix it somewhere else with a query. So that's the basics of how to use the NZ function. For members only, I've got some extra tips and tricks in an extended cut video. First, I'll show you how to set the country to default to United States or whatever country you want if they don't have a country. That's handy for mailing lists. I'll show you how to use NZ to bring a default date into your function. For example, if you're doing gift cards and you want to figure out how many years they've been a customer, I'll show you the DateDiff function to calculate the number of years they've been a customer. Then we can put in today's date if they don't have a date, so they get a zero for that instead of sending them an expensive gift card. One of the problems with calculating order totals is if a customer doesn't have any orders in the database, then you have to make sure you use a left join to bring in a zero value for their orders, and we'll use the NZ to change that null value into a zero for customers that don't have orders in a related table. I'll show you that for the members only video as well. Finally, this is actually a popular one. If you type in an email address and it already exists in the database, you can use a DLookup to check to see if it exists. So if someone types in an email address that's already in there, it'll pop up a warning message and say, this email address already exists. Are you sure you want to add it? We'll use DLookup to check if it exists. We'll use the before update event so we can cancel it. We'll prompt with a message box, ask the user if they want to add it or not. This gives you the flexibility so you can have multiple email addresses that are duplicates in your table, but you don't have to. It'll warn you. Sure, you can index the field and set it to no duplicates, but there's a lot of reasons why I don't like doing that. I explain why in the members video. The whole point of NZ for this function is because DLookup always generates errors if you go to look something up and it's not there. It'll pop up an invalid use of null. NZ gets rid of that. How do you become a member? Click the join button and then pick your membership type. Silver members and up get access to the extended cut videos for all my TechHelp stuff. Platinum members get lots of free full courses. I'll also be doing some live streaming and some members only chat as well. But don't worry, I'll still be making these free TechHelp videos for everyone to enjoy. I just go into more detail for the members. Make sure you subscribe to my channel and click the little bell. Pick "All" to get notifications when I release new videos like this. Make sure you head over to my website and subscribe to my forum as well. I post lots of stuff there. If you haven't already, check out my free Access Level 1 class. If you're getting started with Access or even if you've been using it for a little while and want to see if there's some stuff that you don't know, it's a three hour long free tutorial. It's on YouTube and on my website. There's the link. If you like that, you can get Level 2, which is another full hour course for just one dollar. Thanks for watching, and I hope you learned something. We'll see you next time. QuizQ1. What is the primary purpose of the NZ function in Microsoft Access?A. To convert null values into a default value like zero or another specified value B. To prevent duplicate records from being added to a table C. To change data types within a table or query D. To enforce referential integrity between tables Q2. Why might you choose not to set a field as required in your table design? A. It keeps the database from running slowly B. Users might not know the correct value to enter, so blank (null) indicates unknown information C. It ensures zero is always stored as a default D. It makes the field read-only Q3. In a query, what happens if you add two fields together and one of them is null? A. The result will be null B. The result will be zero C. The result will be the value of the non-null field D. Access will automatically correct the null to zero Q4. How does the NZ function affect calculations that involve possible null values? A. It replaces null with a specified value, such as zero, preventing null results in calculations B. It only identifies, but does not affect, null values C. It deletes records that contain nulls D. It is only used to display messages, not affect calculation results Q5. If you import data into Access and set a field as required, but the import source has missing values for that field, what is a likely outcome? A. The import will skip records missing those required field values B. Access will fill in missing values with zero C. The import will duplicate any missing values D. The import will automatically prompt for missing data Q6. Why is it typically better to handle nulls with queries or forms instead of enforcing required fields at the table level? A. To provide flexibility for old records and imported data where some values may be rightly unknown B. To make all fields default to zero automatically C. To prevent the database from saving duplicate records D. To enforce data validation directly when importing Q7. What will NZ(VAL1, 0) + NZ(VAL2, 0) do in a query if either VAL1 or VAL2 is null? A. It will treat any null value as zero for the addition B. It will always produce a null result C. It will produce an error message D. It will concatenate the value fields as text Q8. When is it appropriate to use the NZ function in conjunction with DLookup? A. When a lookup might return null, to avoid errors in your code or forms B. Only when adding new fields to a form C. When changing the data type of a lookup field D. Only with numeric fields, not with text fields Q9. What is a potential problem with wrapping the entire sum (VAL1 + VAL2) in a single NZ function, as opposed to wrapping each value separately? A. If both are null, result will be zero, but if only one is null, the other value will be ignored B. It multiplies the values instead of adding them C. It prevents any calculation from taking place D. Access will ignore all null fields Q10. Which of the following is NOT a described use case for the NZ function in the video? A. Setting a default country value in a mailing list B. Ensuring report calculations do not return nulls C. Eliminating duplicate records from a table D. Preventing errors with DLookup when no value is found Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-C 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 using the NZ function in Microsoft Access. I am your instructor, Richard Rost.The topic for today comes from a question about how to handle calculations when values might sometimes be deleted or left blank in a table, especially when adding up things like employee work hours on a project. There are a few ways to address this issue. One way is to set the field's property to "Required" in your table, which means the user must always enter a value and cannot leave it blank. This property can be useful but should be used sparingly. For some types of data, leaving a field blank actually provides important information. For example, if you do not know how many children a customer has, leaving the value as null might make more sense than entering zero, which would indicate that you know for a fact that they have none. Similarly, with project hours or similar scenarios, a zero may not always be the appropriate default. When you need your calculations to treat missing (null) values as zero, that's where the NZ function is helpful. The NZ function allows you to specify a default value that should be used if a particular field is null. To demonstrate this, imagine a simple table with an ID field and two numerical value fields. Even if you set their default values to zero, users can still delete the contents, resulting in nulls. If you try to add these two values together in a query or on a form, and one is null, the result will be null too. This is not ideal when you want to total up hours or other values. The NZ function solves this by substituting a specified value (such as zero) wherever a field is null. In practice, you would use NZ around each value you want to add. For example, you would wrap both fields with NZ set to zero. This tells Access to treat any missing value as zero during the calculation. You generally want to apply NZ to each individual value rather than the sum, because wrapping the entire sum in NZ only helps if the entire result is null, which may not be what you want for partial data. This approach makes your calculations consistent and helps avoid issues when older records or imported data might be missing some values. It's especially helpful if you are importing data from other systems where blank fields are common, or when you cannot know whether a null indicates "zero" or simply "unknown." Additionally, relying heavily on required fields can create headaches with imports. If a field is required and your imported data is missing values, those records may not be imported at all, and you might lose important data. The NZ function is useful not just for adding numbers. In the extended cut for members, I show how to use NZ to set default country selections, supply default dates for calculations, or to display a zero order total for customers with no orders, using left joins and NZ together. You'll also see how to check for duplicate email addresses in your database with DLookup and use NZ to suppress errors when the value is not found, providing smoother user prompts and better control over your data. If you're interested in seeing all these techniques in action with step-by-step instructions, including using DLookup with the NZ function to handle missing data gracefully without errors, you will find all of this covered in detail in the extended cut available for members. As always, if you want to support the channel and get access to exclusive extended cut videos, you can join as a Silver Member or above. Platinum members have access to all of my full courses. Regardless, free TechHelp videos will always be available on YouTube for everyone. If you're new to Microsoft Access or would like to strengthen your foundation, be sure to check out my free Access Level 1 class, available both on YouTube and my website. If you enjoy that, there's also an affordable Level 2 course you can take next. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListSetting required fields in Access tablesUnderstanding default values in table fields Implications of storing null vs zero in tables Adding numeric fields together in a query Behavior of null values in arithmetic operations Using the NZ function to handle nulls in calculations NZ function syntax and placement in expressions Difference between wrapping individual fields vs entire expressions in NZ NZ function use when importing data with nulls NZ function use with legacy data containing nulls Avoiding required fields to prevent data import issues Best practice for handling missing data in user entries |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access NZ Null Zero Exists null to zero nz function PermaLink NZ Function in Microsoft Access |
