Microsoft KB Archive/209715

= ACC2000: Query by Form Returns No Rows When All Rows Expected =

Article ID: 209715

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209715



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
When you specify criteria for a query in a previously designed form, you leave one of the criteria fields on the form blank because you do not want to filter the records by the data in that field, only by the criteria you entered in other fields on the criteria form. But instead of getting all the records, as you expected, you get none at all.

For example, you want all employees who live in London, regardless of their last name, so you type London in the City field and leave the Last Name field blank. But instead of getting the records of all London-based employees, as you expected, you get none at all.



CAUSE
Queries based on forms that contain empty criteria fields may return unexpected results because an empty criteria field results in the following condition in the WHERE clause:

Like Null

This condition is always false because any operation that includes a null value returns a null result.

For example, an application developer might put the following criteria in the City field in a query:

Like Forms!EmployeesSearch!City

EmployeesSearch is a form that holds the criteria fields. Someone using the application might omit a entry in the City field in the EmployeesSearch form in the hopes of seeing all the rows, regardless of which city. However, the query actually returns no rows at all because of the null reference in the resulting WHERE clause.



RESOLUTION
Application developers can work around this potential problem by either of two methods.

Method 1: Use the Nz Function
The criteria statement for the City field in a Query By Form on data from the Employees table should read

Like Nz(Forms!frmEmployeesSearch!City,"*")

The Nz function returns a specified value if the field is empty. In this case, the function puts an asterisk in each City field to indicate that the query will accept any value in this field.

Method 2: Using the Filter By Form Feature
Microsoft Access 2000 includes a feature called Filter by Form. You can use this feature to filter the current form based on criteria entered by the user. When Filter by Form is invoked, a blank copy of the form is displayed for entering criteria. You just leave the fields blank that you do not want to put criteria in and those fields are ignored when the filter is applied.

