Null values in a Iif statement

Question

I have written a report by form. The form basically asks for a start date, end date and a technician number. These values are passed to the underlying query in a report. I want to be able to run the report for either a single technician number, or leave the field null (in the form) and run for all technicians.

When I place this statement in the criteria in the underlying query:

IIf([Forms]![WO Labor Report Form]![MID] Is Null,Null,[Forms]![WO Labor Report Form]![MID])

The report runs fine as long as there is a value in the form field. If I null out the form field, no values are selected.

I then tried this statement:

IIf([Forms]![WO Labor Report Form]![MID] Is Null,"> 0",[Forms]![WO Labor Report Form]![MID])

I get the following error when the field in the form is null:

This expression is typed incorrectly, or it is too complex to be evaluated. For Example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

How do I write the Iif statement to run for all values when Null?

Your help is appreciated,

Bruce

Answer 1

The basis making a parameter optional is that in the query's WHERE clause each parameter is tested in this way:

WHERE (SomeColumn = <some parameter>

OR <some parameter> IS NULL)

AND (SomeOtherColumn = <some other parameter>

OR <some other parameter> IS NULL)

AND etc

The above uses equality operations, but the operations can be of any type.

Each OR operation is enclosed in parentheses to force it to evaluate independently.  These parenthesized expressions are then tacked together with AND operations.  The way it works is that each parenthesized OR expression will evaluate to TRUE for each row where the value in the column is that of the parameter, or if the parameter is left empty (NULL),  for every row.  By virtue of the AND operations  the WHERE clause as a whole will evaluate to TRUE for those rows where all of the parenthesized expressions evaluate to TRUE, so those rows will be returned.

Note that when you do this parameters should only be declared if they are of DateTime data type.  If other types were declared they could never be Null.  DateTime parameters are unusual in this respect, and it's always advisable to declare them to avoid their being misinterpreted as arithmetical expressions rather than dates.

When building a query like this, the basic unrestricted query can be built in query design view, but the WHERE clause should always be written and, most importantly, saved in SQL view.  This applies to the initial saving of the query, and if any subsequent amendments are made.  If it's saved in design view Access will move things around and at best the logic will be obscured, at worst it might become too complex to open.  It's a good idea to save the SQL of such queries in a text file in Notepad or similar, as if anything does go wrong you then have something to copy and paste back into the query designer in SQL view.

So applying this to your scenario a query would be along these lines:

PARAMETERS [Forms]![WO Labor Report Form]![txtStartDate] DATETIME,

[Forms]![WO Labor Report Form]![txtEndDate] DATETIME;

SELECT *

FROM [YourTable]

WHERE ([MID] = [Forms]![WO Labor Report Form]![MID]

OR [Forms]![WO Labor Report Form]![MID] IS NULL)

AND ([YourDateField] >= [Forms]![WO Labor Report Form]![txtStartDate]

OR [Forms]![WO Labor Report Form]![txtStartDate] IS NULL)

AND ([YourDateField] < [Forms]![WO Labor Report Form]![txtEndDate]+1

OR [Forms]![WO Labor Report Form]![txtEndDate] IS NULL);

Note how the date range is defined as on or after the start date and before the day following the end date.  This is safer than a BETWEEN....AND operation as it caters for the possibility of rows with a date on the final day of the range with a non-zero time of day.  Such date/time values cannot be confidently ruled out unless specific provision has been made in the table design to exclude them by means of the ValidationRule property of the date/time field in question.

Answer 2

Ken,

Thank you very much! Worked great, once you laid it out, it reminded me that that this was the way we had to do it using COBOL. I was trying to use the grid in design view, but once I placed your suggestion in SQL it worked. One comment: had to code where [MID] resided.

This is a great site, with great people helping others, thanks again!

Bruce

Answer 3

A potential problem with that approach, Karl, is that it will not return a row with a NULL at the relevant column position.  Also, in this instance, it's comparing a value of what I assume to be number data type with a string expression, and is less efficient because it prevents the use of the indexes.  The simple Boolean logic of:

(SomeColumn = <some parameter>

OR <some parameter> IS NULL)

covers all bases.

The alternative is not to use parameters, but to build a string expression in code by examining each control in turn for NULL, and filtering the report by means of the WhereCondition argument of the OpenReport method.  This won't work if the report is being output to another format such as a PDF file by means of the OutputTo method, however, in which case parameters are the way to go.

Answer 4

Try this --

   LIKE IIf([Forms]![WO Labor Report Form]![MID] Is Null, "*",[Forms]![WO Labor Report Form]![MID])