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

• 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

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

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

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

   Like CNulls( Forms!CriteriaDialog!CustomerName, "*"  )

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:

   ARTICLE-ID: Q95931
   TITLE     : ACC: How to Use the Query by Form (QBF) Technique