Microsoft KB Archive/94027

= ACC: Query By Form Returns No Rows When All Rows Expected =

Article ID: 94027

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q94027



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



SYMPTOMS
When you specify criteria for a query in a previously designed form, you leave one of the fields on the form blank because you want to view all the records, not just the ones that match a certain value. But instead of getting all the records, as you expected, you get none at all.



CAUSE
Queries 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.

Queries can reference form fields through implicit parameters--making a query by form. For example, an application developer might put the following criteria on the CustomerName field in a query:

  Like Forms!CriteriaDialog!CustomerName

CriteriaDialog is a form that holds the criteria fields. Someone using the application might omit a customer name entry in the CriteriaDialog form in the hopes of seeing all the rows. However, the query actually returns no rows because of the null reference in the resulting WHERE clause.



RESOLUTION
Application developers can work around this potential problem by adding the following function to a module:

Function CNulls (v As Variant, subs As Variant) As Variant If (IsNull(v)) Then CNulls = subs Else CNulls = v     End If   End Function

The CNulls function converts null values to a given value. Essentially, if the first argument to the function is null, the second argument is returned. Otherwise, the first argument is returned unchanged.

After you add the function to a module, change the criteria to read as follows:

  Like CNulls( Forms!CriteriaDialog!CustomerName, &quot;*&quot;  )

Now, if the user does not supply a customer name, the CNulls function will return the asterisk, and the condition will return all rows that contain data.

Alternative Solutions for Microsoft Access 7.0 and 97
In Microsoft Access 7.0 and 97, you can replace the CNulls function described above with the NZ built-in function. The NZ function accepts the same arguments as the CNulls function. For more information about the NZfunction, search the Help Index for &quot;Nz function.&quot;

-or-

Microsoft Access 7.0 and 97 include 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 wish to put criteria on and those fields are ignored when the filter is applied. For more information about Filter by Form, search the Help Index for &quot;Filter by Form.&quot;

