ACC2000: Query by Form Returns No Rows When All Rows Expected (209715)

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.

REFERENCES

For additional information about using the Query By Form technique, click the article number below to view the article in the Microsoft Knowledge Base:

209645 ACC2000: How to Use the Query by Form (QBF) Technique

For more information about the Filter by Form method, click Microsoft Access Help on the Help menu, type filter records by entering values in a blank view of your form or datasheet in the Office Assistant or the Answer Wizard, and then click Search to view the topic.