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

ID: Q94027

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

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

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, "*"  )

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 NZ()function, search the Help Index for "Nz function."

-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 "Filter by Form."

REFERENCES

For more information about using the Query By Form technique, please see the following article in the Microsoft Knowledge Base:

Q95931 ACC: How to Use the Query by Form (QBF) Technique

Additional query words: handling nulls as param

Keywords : kbusage QryOthr
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbprb