Access Tip of the Day
Easily substitute strings in place of Null values (Access 97/2000/2002)
Although you may make a best effort to eliminate Null values from table fields, it's inevitable that some records eventually have fields that are Null. Null fields may not necessarily be a problem at the table level, but they can be a nuisance when you view a query and see unexplained blank cells in a datasheet. If you work with the data programmatically, Null fields can break your procedures. Even worse, fields aren't the only area of concern when you're creating VBA procedures -- Null Variables can be equally troublesome. You may deal with such problems by incorporating special error-handling or using conditional statements and the IsNull() function to control the procedure flow, you can often use an easier method to deal with such situations.
Instead of building conditional statements with If...Then statements or the IIf() function, you can use the Nz() function. This functions specific purpose is to return a string in place of Null. The full syntax is:
Nz(Value,ValueIfNull)
The first argument is the variable or field you want to check. The second argument is the string that should be returned when Value is Null. For example, say you have a query that retrieves data from a field named Revenue. If you enter the expression
Rev: Nz([Revenue],"Information Unavailable")
as one of the query's fields, the Rev column will display the message "Information Unavailable" if the Revenue field is Null, otherwise the value stored in Revenue is displayed in the query datasheet as usual.
Note that if you leave the ValueIfNull argument blank, a zero-length string is automatically returned when Value is Null.
(Content provided by Element K Journals)
(Note: Your browser is set to refuse cookies. As a result, you may frequently see previously-viewed tips)
Print
CloseCopyright © 2001 - 2025 MJ Technologies, LLC. All rights reserved.