Microsoft Access Handling NULL Values with the NZ Function

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?"

BONUS FOR CHANNEL MEMBERS: Silver Members and up get access to an EXTENDED CUT of this video which covers additional examples. If the customer doesn’t have a country specified, set it to "United States." 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. TOPICS: Calculated Query Fields, DateDiff, IIF, Relationships, Inner Joins, Aggregate Queries, DLookup, Before Update, MsgBox Return Value

RESOURCES:

[SophieC-]

Thank you so much! This is exactly what I’m looking for !

[hrzctgbd]

its helped a lot, Boss. Thank you very much.

[davidshumway]

Found this while trying to solve my own issue and thought I would offer a solution. When testing an equation for null wrap it in parenthesis. Nz(([Val1] + [Val2]), 0)

[dottiearnold]

Very helpful! I have a report that I created that calculates the percentage of two fields %Placed, which returns a #Num! because the Grad field is currently. Is there a way to make it appear as zero or blank until the Grad field is populated?

Field Names Values
Placed query calculation totaling up # of students placed
Grad query calculation totaling up # of graduated students
%Placed =[Placed]/[Grad]

[jasonfrank]

NZ isn't compatible with Excel. Is there another way to add the 0's in queries?

[apupatra]

how to print label when data is available

[-selfdevelopment]

I have a problem pleace haw i can contact

[cambodeeritnews]

hi instructor. could help me a little bit? if you would i appreciated because i am stuck in this access query.
here is query => SELECT TblTable.TblNameKhmer as TableNo, (Select sum(total) from TblCart where TblID=TblTable.TblID and status Like 'Pending' group by TblID) AS Bill
FROM TblTable;

it shows me like this: Table 01 20.00
Table 02 30.00
Table 03 Null ( i need your help right here. so i want this Null to be 0.00 and i don't know what to do.

[kensimba]

Asanti Sana ( Swahili way of saying thanks very much)
Swahili response should be 'karibu'