Making Date Fields Optional in Access Queries

If you would like to give a person optional choices in a date parameter, and don’t want to use if statement logic, the NZ function is the way to go. Recently in Access, I had to write a query that was getting criteria from a form called frmChoice. I wanted to give my database user the option to enter a starting date (in a field called dtmStartDate)  and/or an ending date (in a field called dtmEndDate). If they chose not to enter a date, I wanted to use a start that was well beyond the first date in the data, and if they chose not to enter an ending date, I wanted to automatically supply the current date as the ending date.  With non-optional dates, the criteria line would look something like this:

Between [forms]![frmChoice]![dtmStartDate] and [forms]![frmChoice]![dtmEndDate]

However, if no date was entered on the form, the above statement will return an error.

Enter the NZ function. In the help, Microsoft describes the NZ function this way:

”You can use the Nz function to return zero, a zero-length string (zero-length string: A string that contains no characters. You can use a zero-length string to indicate that you know no value exists for a field. You enter a zero-length string by typing two double quotation marks with no space between them (” “).) (” “), or another specified value when a Variant (Variant data type: The default data type for variables that don’t have type-declaration characters when a Deftype statement isn’t in effect. A Variant can store numeric, string, date/time, Null, or Empty data.) is Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can’t contain 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] )

So the criteria statement becomes:

Between NZ( [forms]![frmChoice]![dtmStartDate], #01/01/2000#)  and NZ([forms]![frmChoice]![dtmEndDate], Date())

In this case, the NZ function will force the starting date to be January 1, 2000 if left blank, and force the ending date to be the current date if left blank on the form by the user.

The NZ function can also be used to make a maximum or minimum value an optional choice. Suppose you recorded meeting attendance in a database. Sometimes, you wanted to find all the meetings where more than 15 people attended, but sometimes, you just wanted to see all of the meetings regardless of attendance. In the criteria line of the query you could enter:

>=NZ([forms]![frmChoice]![lngAttendance], 0)

In this case, if someone enters a number, you will use that figure as your minimum. If they leave the field blank, zero will be used as the minimum.

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically each day to your feed reader.