Filtering a ACCESS Form using VBA problem empty fields

[G04]

Hi All,

I have a continuous form with all records. For each field there is a combo in the form header and the form also contains a Toggle button "Apply Filter".
When clicked, it changes to "Cancel Filter".
Then an SQL string with condition meeting the criteria input in the combo boxes is built.

I have noticed that when certain fields are not filled in (no space, nothing), the record is simply not shown. The empty field is not necessarily one with a criteria.

Here goes the (simplified) code:

Select Case Me.ToggleApplyFilter.value 'check whether clicked?
Case True
    Me.ToggleApplyFilter.Caption = "Cancel filter"
    strSQL = "SELECT CustomerComplaintForm.* FROM CustomerComplaintForm WHERE ("
'ID
    If Len(Trim(Me.cboIdFilter.value)) = 0 Or IsNull(Me.cboIdFilter) Then
        strComplaintIDCondition = "((CustomerComplaintForm.ComplaintId) Like '*') AND "
    Else
        strComplaintIDCondition = "((CustomerComplaintForm.ComplaintId) =[Forms]![FM_Customer_Complaints_List]![cboIdFilter]) AND "
    End If
'Customer (Name)
    If Len(Trim(Me.cboCustomerFilter.value)) = 0 Or IsNull(Me.cboCustomerFilter) Then
        strCustomerCondition = "((CustomerComplaintForm.CustomerName) Like '*') AND "
    Else
        strCustomerCondition = "((CustomerComplaintForm.CustomerName) =[Forms]![FM_Customer_Complaints_List]![cboCustomerFilter]) AND "
    End If
    If Len(Trim(Me.cboProductDestinationFilter.value)) = 0 Or IsNull(Me.cboProductDestinationFilter) Then
        strProductDestinationCondition = "((CustomerComplaintForm.ProductDestination) Like '*')"
    Else
        strProductDestinationCondition = "((CustomerComplaintForm.ProductDestination) =[Forms]![FM_Customer_Complaints_List]![cboProductDestinationFilter])"
    End If
 
    strSQL = strSQL & strComplaintIDCondition & strCustomerCondition & strUnitsCondition & _
    strPFICondition & strPOCondition & strCustRefCondition & strAcceptableCondition & _
    strCauseCondition & strCategoryCondition & strSubCategoryCondition & _
    strProductDestinationCondition & ");"
Case False
    Me.ToggleApplyFilter.Caption = "Apply filter"
    strSQL = "SELECT CustomerComplaintForm.* FROM CustomerComplaintForm;"
    Me.cboAcceptableFilter.value = ""
    Me.cboIdFilter.value = ""
    Me.cboCustomerFilter.value = ""
    Me.cboCustRefFilter.value = ""
    Me.cboPFIFilter.value = ""
    Me.cboPOFilter.value = ""
    Me.cboUnitsFilter.value = ""
    Me.cboCauseFilter.value = ""
    Me.cboCategoryFilter = ""
    Me.cboSubCategoryFilter = ""
End Select
 
Debug.Print strSQL
Me.RecordSource = strSQL
 
 

Imagine the field [customer] is empty for a certain record and I only filter on [Product destination]. Well, this record with empty field [customer] will not be in the query.
I already copied the resulting SQL string in an empty query. In design view it gives

 Like '*' 

for all fields for which no criteria was provided in the forms. But the result is the same - wrong. When I deleted this

 Like '*' 

, it gave the correct result. Is there a reason why those fields are excluded?

I adjusted the SQL building so that only the non empty combo's are included in the SQL string. That way there is no

 Like '*' 

any more.
And it works.

It's nice it works, but I want to understand why this access behaviour?

[MMcCarthy]

Have a look at this tutorial and see if it helps ...

http://www.thescripts.com/forum/thread762181.html