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, "*" )
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:
95931 ACC: How to Use the Query by Form (QBF) Technique
Additional query words: handling nulls as param
Keywords: kbprb kbusage KB94027
