Nz Function - Microsoft Support

Skip to main content Sign in with Microsoft Sign in or create an account. Hello, Select a different account. You have multiple accounts Choose the account you want to sign in with. Access Expressions Popular functions Popular functions Nz Function Applies ToAccess for Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

Syntax

Nz ( variant [, valueifnull ] )

The Nz function syntax has these arguments:

Argument	Description
variant	Required. A variable of data type Variant.
valueifnull	Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string. Note: If you use the Nz function in an expression in a query without using the valueifnull argument, the results will be a zero-length string in the fields that contain null values.

If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string. If the optional valueifnull argument is included, then the Nz function will return the value specified by that argument if the variant argument is Null. When used in a query expression, the NZ function should always include the valueifnull argument,

If the value of variant isn't Null, then the Nz function returns the value of variant.

Remarks

The Nz function is useful for expressions that may include Null values. To force an expression to evaluate to a non-Null value even when it contains a Null value, use the Nz function to return zero, a zero-length string, or a custom return value.

For example, the expression 2 + varX will always return a Null value when the Variant varX is Null. However, 2 + Nz(varX) returns 2.

You can often use the Nz function as an alternative to the IIf function. For example, in the following code, two expressions including the IIf function are necessary to return the desired result. The first expression including the IIf function is used to check the value of a variable and convert it to zero if it is Null.

varTemp = IIf(IsNull(varFreight), 0, varFreight)varResult = IIf(varTemp > 50, "High", "Low")

In the next example, the Nz function provides the same functionality as the first expression, and the desired result is achieved in one step rather than two.

varResult = IIf(Nz(varFreight) > 50, "High", "Low")

If you supply a value for the optional argument valueifnull, that value will be returned when variant is Null. By including this optional argument, you may be able to avoid the use of an expression containing the IIf function. For example, the following expression uses the IIf function to return a string if the value of varFreight is Null.

varResult = IIf(IsNull(varFreight), _ "No Freight Charge", varFreight)

In the next example, the optional argument supplied to the Nz function provides the string to be returned if varFreight is Null.

varResult = Nz(varFreight, "No Freight Charge")

Query examples

Expression	Results
SELECT ProductID, NZ(Discount,"No Detail Available") AS Expr2 FROM ProductSales;	Returns "ProductID" in column Expr1, evaluates the 'Null' values in the field "Discount" and returns "No Details Available" for all Null values (returns the non-null values as is).
SELECT ProductID, NZ(Discount,"No Detail Available") AS ReplaceNull FROM ProductSales;	Returns "ProductID" in column Product, evaluates the 'Null' values in the field "Discount" and returns "No Details Available" for all Null values (returns the non-null values as is) and displays in column ReplaceNull.

VBA example

Note: Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer Reference in the drop-down list next to Search and enter one or more terms in the search box.

The following example evaluates a control on a form and returns one of two strings based on the control's value. If the value of the control is Null, the procedure uses the Nz function to convert a Null value to a zero-length string.

Public Sub CheckValue() Dim frm As Form Dim ctl As Control Dim varResult As Variant ' Return Form object variable ' pointing to Orders form. Set frm = Forms!Orders ' Return Control object variable ' pointing to ShipRegion. Set ctl = frm!ShipRegion ' Choose result based on value of control. varResult = IIf(Nz(ctl.Value) = vbNullString, _ "No value.", "Value is " & ctl.Value & ".") ' Display result. MsgBox varResult, vbExclamationEnd Sub

Need more help?

Want more options?

Discover Community

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Yes No Thank you! Any more feedback for Microsoft? Can you help us improve? (Send feedback to Microsoft so we can help.) What affected your experience? Resolved my issue Clear instructions Easy to follow No jargon Pictures helped Other Didn't match my screen Incorrect instructions Too technical Not enough information Not enough pictures Other Any additional feedback? (Optional) Submit feedback By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×