Missing Values
By Richard Rost
3 years ago
Missing Values Cause Empty Fields in Access
In this Microsoft Access tutorial I'm going to teach you how to fix a problem that occurs when missing values on your form result in empty fields for your calculations.
Liam from Omaha, Nebraska (a Platinum Member) asks: I have a form where I enter in a couple of fixed costs for a service order and then I itemize the services performed to get a total. The problem is it all works great unless one of those things is missing, then I get a blank result. What am I doing wrong?
Prerequisites
Links
Recommended Courses
Usage
- =Nz([SetupFee])+Nz([AnotherFee])+NZ(OrderDetailF.Form!SumExtPrice)
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, missing value, null, nz, blank text box from calculations, Calculated Field Not Showing Value
Subscribe to Missing Values
Get notifications when this page is updated
Intro In this video, we will look at how missing values can affect your calculations in Microsoft Access, leading to unexpected blank results in your forms and reports. I will show you how to identify when null values are causing these problems, demonstrate how to use the NZ function to handle nulls in calculated fields, and walk you through working with fixed costs and subform totals to get accurate grand totals even when some fields are empty.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how missing values can cause your calculations to result in empty fields in your Microsoft Access databases. Here we go.
Today's question comes from Liam from Omaha, Nebraska, one of my platinum members. Liam says, I have a form where I enter in a couple of fixed costs for a service order and then I itemize the services performed to get a total. The problem is it all works great unless one of those things is missing, then I get a blank result. What am I doing wrong?
Well, Liam, I suspect that your function isn't written to take null values into consideration. Let's walk through it step by step.
First, if you haven't watched my video on the NZ function, go watch this first. This shows you how to take a null value and convert it to zero. Also, since you are able to get a total if you have line items in your itemized subform, you probably know how to do this. For the rest of you, if you don't know how to add up and get a value from a subform, go watch this video. We're going to do that today as well.
Liam's subform - he sent me a picture of it in the email - looks very similar to my order form here where you've got a list of items. These are our order detail items, and you add these all up and get a total down here. In addition to this, Liam has a couple of fixed costs. Let's say the order has a couple of fixed costs associated with it first, like maybe a delivery fee, a service fee, or whatever. Then, he adds these up and gets a grand total at the bottom on the parent form.
So let's add a couple of fields real quick to the order table design view. By the way, if you don't know how to build this stuff, go watch my invoicing video. I show you the complete steps to build this entire order entry system. It's all free. It's on my YouTube channel and on my website. Go watch it. Lots of good material there.
Let's say we have two things here. Let's say we have a service fee and a delivery fee. I don't know; maybe we've got another fee, an extra fee, and a fee for the fee, and the tax for the fee, and you get the point. There are two additional fixed costs per order, which is similar to what Liam's doing.
Add those to the form design view. I'm right down here. We've got the subtotal for the items. Then we'll add the extra fees to the parent form because these are part of the order, not the order details. So let's go to form design, go to add existing fields, grab those two fees that we just added, and put them down here.
There's our service fee and our delivery fee. We'll make these black so we can actually read them. Service fee, delivery fee. Save it. Close it. Open it.
They're both null right now. Let's put values in here. I'll put 15 and 20.
Now, we want to add these three things up: the service fee, the delivery fee, and that total from the subform. We want to add these all up and get a grand total for the order. This will be another calculated field.
Why calculated? We don't need to store this value in the table because we can calculate it on the fly. There are exceptions to the rule, but generally, you want to just leave calculated fields so you calculate them when you need them. You don't want to store these in the database, generally, 99 percent of the time.
I'm just going to copy one of these controls, copy and paste, and we'll make this the grand total. Grand total. We'll hold it, and we'll change this control so it's going to be a calculated field now. We'll call it grand total.
What's the control source going to be? Well, it's not bound to a field anymore, so delete that. Let's start off with just the service fee and the delivery fee, just those two things first.
Zoom in so you can see this better. Shift+F2. It's going to be equal to the service fee plus the delivery fee. Just that for now. Hit OK. I'll bold that too. Let's click this so you can tell it's the total, and I like to make calculated values gray in the background, so let's go with the light gray.
Close it. Open it. There's my $35.
Now, here's the problem. If one of these things is missing, delete that value, and look at that - grand total is blank now.
Why? Because that's a null value. As we know, if you add null to anything, the result is null. That's just how null works. If you have a bunch of numbers, you add them all up, and one of them is null, the whole result is null.
If you want to learn more about how null works, go watch this video. Null math is pretty cool, too. I teach you some tricks about null math. Go watch that as well.
How do we fix this problem? We fix this problem with the NZ function. Let's go back to this control. Go back to it. Zoom in, Shift+F2. We can wrap these values inside of NZ like that. If you want the default to be zero, just leave it. You can leave it like that or you can specify comma zero if you want to specify a value. You can say, OK, if it's null, make it 10, whatever. Or if this is a string, you could say make it an empty string. Or if it's a date, you could say make it 1/1/1900, whatever. But I'm going to leave it like that. NZ will make it zero, since that is a currency value. Currencies and numbers will default to zero. That's why it's NZ - null to zero, or null to zero-length string, I guess.
Hit OK. Save it. Close it. Always save it. Close it. Open. I don't like switching between form view and design view. I'm back to form view.
Now look at that. Now my service fee is null, but it still shows up as 20 down there. You could use a little formatting if you want. If this is null, it'll still show a zero there. That's a formatting trick. I actually covered how to do that in yesterday's video, which is format currency. Go watch that. I'll put a link down below.
So that handles these two fees. Let me put a value back in here. Now let's add this one.
Now this comes into knowing how to get a value from a subform.
How do we get a value from a subform? If you watch the other video, you'll know that it's the name of the subform, which is OrderDetailF, and then .Form! field name, which is SumExtendedPrice.
Yeah, I know it's a lot. So let's come back in here again. We're going to add to that. What is it? It's going to be OrderDetailF (that's the name of the subform).Form!SumExtendedPrice.
Hit OK. Save it. Close it. Open it.
Look at that. Now that adds that in. Ninety percent of Access is just knowing what to call stuff and making sure you have field names spelled properly.
Now, again, you've got the same problem: if you don't have any line items in there and this value is null, you're back to null again.
How do we fix that? We can put an NZ in here, or we can also put an NZ right in here, whichever one you want. If you don't want this one being empty, you could NZ this whole thing, or just put the NZ in this control. Put it right there: NZ. Then, at the end, hit OK. Save it. Close it. Open it.
We're back to 30. Put something else in here - go, go, go, go, go - 15. There you go.
That's pretty much it. It's a combination of knowing how to name something, how to get a value from a subform or a different form. I covered that in another video as well. And properly using the NZ function so you can deal with your null values.
A lot of this stuff is just putting the Legos together differently. I've already covered all this stuff in different videos, but sometimes it doesn't all click until you see it fixing your problem.
I know that's how my brain works. I might see something in there, think, Oh yeah, I did learn about that a year ago. Now it makes sense.
But that's it. That's your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main issue Liam is experiencing when calculating his grand total in Microsoft Access?
A. The calculation is not updating automatically when new items are added.
B. The grand total field shows a blank result if one component value is missing.
C. The calculation includes taxes when it should not.
D. The service fee and delivery fee cannot be added to the total.
Q2. What causes a calculation to return a blank (null) result in Access when adding multiple fields?
A. If any numeric value is negative.
B. If any field involved in the calculation contains a null value.
C. If the total exceeds a specific amount.
D. If the fields are in different tables.
Q3. What function does Richard recommend to convert null values to zero in Access?
A. SUM
B. ISNULL
C. NVL
D. NZ
Q4. Why is it usually not necessary to store calculated fields such as a grand total in the database?
A. Calculated fields can be exported easily.
B. Storing calculated results wastes storage and can cause inconsistencies.
C. Calculated fields cannot be displayed on forms.
D. It is impossible to store any calculated result in Access.
Q5. What is the purpose of using the NZ function in calculations in Access?
A. To count the number of non-null records.
B. To replace null values with a default, such as zero, to avoid calculation errors.
C. To validate data types in a field.
D. To join two tables based on null values.
Q6. In the context of Access forms, what is the correct syntax to reference a value from a subform control called SumExtendedPrice on a subform called OrderDetailF?
A. OrderDetailF!SumExtendedPrice
B. OrderDetailF.Form!SumExtendedPrice
C. Forms!OrderDetailF!SumExtendedPrice
D. [OrderDetailF].[SumExtendedPrice]
Q7. When adding fields from the order table for things like service fee and delivery fee, where should these fields be placed?
A. In the order details table.
B. On the parent order form.
C. Only in the query, not on any form.
D. In a separate unrelated table.
Q8. What happens if you add a null value to a number in Access?
A. The result will be the original number.
B. The result will be zero.
C. The result will be null.
D. Access will raise a calculation error and stop processing.
Q9. Which approach should generally be taken for calculated values like totals and grand totals in database design?
A. Always store them in the table for future reference.
B. Only calculate them on reports, not on forms.
C. Calculate them as needed rather than storing them.
D. Ignore them as they are not important.
Q10. According to Richard, what percentage of Access is just knowing what to call stuff and naming things properly?
A. 30 percent
B. 50 percent
C. 70 percent
D. 90 percent
Answers: 1-B; 2-B; 3-D; 4-B; 5-B; 6-B; 7-B; 8-C; 9-C; 10-D
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.Summary Today's TechHelp tutorial from Access Learning Zone addresses a common challenge in Microsoft Access databases: how missing or null values can cause your calculated fields to display blank results. If you have ever noticed that one missing entry turns your whole calculation blank, it is almost always an issue with null values.
The question came in from one of my platinum members who has a form for entering fixed costs on a service order. He itemizes services in a subform to get a total, but everything falls apart if one of the fields is blank – suddenly, the calculation just disappears instead of showing a number.
This situation is familiar to many Access users. In Access, if you add numbers together and any one of them is null (meaning it is empty or missing), the entire result becomes null. That is simply how nulls work; null plus anything is always null.
To demonstrate a solution, let me walk you through an example. Imagine an order form where you have a subform with all your detail items, and then, in addition to those, you need to add a couple of fixed costs like a service fee and a delivery fee to the parent order form. You want the grand total to include all of these: the sum of the itemized services and the two fixed costs.
First, I added two new fields to the order table: one for the service fee and one for the delivery fee. Once these were in the table, I added them to the main order form. Initially, with both fields empty, entering values for the two fees calculates as expected. For example, if you enter a service fee of 15 and a delivery fee of 20, the sum is 35.
However, here is where the issue crops up. If you erase one of those fees (making it blank or null), the grand total immediately becomes blank as well. That is because in Access, any arithmetic operation involving a null returns null.
The key to solving this problem is to use the NZ function. This function takes a null value and converts it to something usable—usually zero for numeric or currency fields. By using NZ around each field in your calculation, you make sure that if a value is missing, it gets treated as zero instead of wiping out your entire calculation.
So, in the control source for your grand total, instead of simply adding the service fee and delivery fee, you use NZ around each one. Now, even if one or both fields are empty, the calculation still works, and missing values are simply counted as zero. This approach works whether you specify a default value or just let NZ default to zero for numbers.
If you want to extend the calculation to include a total from a subform (like the sum of line items), you need to reference that subform's total field accurately. You use the format "SubformControlName.Form!FieldName" in your calculation. Once you add the subform total to the equation, you might find the same null problem arises again if there are no items in the subform. The solution is the same: wrap the reference to the subform's total in the NZ function.
With this setup, your grand total field will reliably sum up all the components, even when some are missing. Properly using NZ helps your forms behave intuitively and keeps your totals visible at all times.
Mastering these concepts is a big part of becoming proficient with Access forms, especially when working with subforms and calculated controls. Remember, you usually do not need to store calculated values in tables unless you have a specific reason—calculations can almost always be done on the fly with correctly structured forms.
The important takeaways here are understanding how nulls behave in calculations and knowing how to use the NZ function to guard against them. These techniques are covered in detail in my other videos as well. Often, the challenge is just about knowing what to call each field or control and putting all the pieces together.
If you want to see the full video tutorial that walks you through these steps in detail, including working with table and form design, head over to my website using the link below.
Live long and prosper, my friends.Topic List How null values affect calculations in Access
Adding fixed cost fields to an Access table
Placing fixed cost fields on an Access form
Creating a calculated control for grand total
Handling null values using the NZ function
Combining parent form fields and subform totals
Referencing values from a subform in calculations
Formatting calculated fields for currency
Preventing blank results from null subform totals
Using NZ to default nulls to zero in calculations |
