Filter does not return null value in reordset

[ezybusy]

Hi everyone.


I need your help for an issue regarding the filtering option of one of my form.

I have a query based on three (3) table, which i used as the record source for a form that i named "Frm_FertilizerBrand".

Now i have three (3) combo boxes and two buttons (Search and Clear) which i use to perform filter in order to allow the user to view a set of record based on the value(s) selected from the combo boxes.
Scenario
----------
1- User select a value from one or all combo boxes
2- User clicks "Search" button to perform the filter
3- User click "Clear" to remove filter and show all records

I have the following Vba code:

On the button "Search"
--------------------------
Private Sub Search_Click()
DoCmd.OpenForm "Frm_FertilizerBrand", acNormal, , "FertilizerHs Like '" & Me.CboFertilizer & "*' AND Country Like '" & Me.CboCountry & "*' AND ImporterID Like '" & Me.CboCompany & "*'"
End Sub



On the button "Clear"
-----------------------
Private Sub Clear_Click()
DoCmd.OpenForm "Frm_FertilizerBrand", acNormal
DoCmd.ShowAllRecords
Me.CboFertilizer = Null
Me.CboCountry = Null
Me.CboCompany = Null
End Sub


The Problem
--------------
The issue is the filtering option does not display the the records where one the field has no value (Null).
Example: when i perform a filter for all Fertilizer_Products in a particular country, the result returned does not include countries where ImporterID is null

How can i solve this??

[June7]

Options:

1. handle the null by calculating a field that returns alternate value and apply filter to that constructed field

2. include Or Is Null in the filter criteria for each field