By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

Filtering a ACCESS Form using VBA problem empty fields

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. Select Case Me.ToggleApplyFilter.value 'check whether clicked?
  2. Case True
  3.     Me.ToggleApplyFilter.Caption = "Cancel filter"
  4.     strSQL = "SELECT CustomerComplaintForm.* FROM CustomerComplaintForm WHERE ("
  5. 'ID
  6.     If Len(Trim(Me.cboIdFilter.value)) = 0 Or IsNull(Me.cboIdFilter) Then
  7.         strComplaintIDCondition = "((CustomerComplaintForm.ComplaintId) Like '*') AND "
  8.     Else
  9.         strComplaintIDCondition = "((CustomerComplaintForm.ComplaintId) =[Forms]![FM_Customer_Complaints_List]![cboIdFilter]) AND "
  10.     End If
  11. 'Customer (Name)
  12.     If Len(Trim(Me.cboCustomerFilter.value)) = 0 Or IsNull(Me.cboCustomerFilter) Then
  13.         strCustomerCondition = "((CustomerComplaintForm.CustomerName) Like '*') AND "
  14.     Else
  15.         strCustomerCondition = "((CustomerComplaintForm.CustomerName) =[Forms]![FM_Customer_Complaints_List]![cboCustomerFilter]) AND "
  16.     End If
  17.     If Len(Trim(Me.cboProductDestinationFilter.value)) = 0 Or IsNull(Me.cboProductDestinationFilter) Then
  18.         strProductDestinationCondition = "((CustomerComplaintForm.ProductDestination) Like '*')"
  19.     Else
  20.         strProductDestinationCondition = "((CustomerComplaintForm.ProductDestination) =[Forms]![FM_Customer_Complaints_List]![cboProductDestinationFilter])"
  21.     End If
  22.  
  23.     strSQL = strSQL & strComplaintIDCondition & strCustomerCondition & strUnitsCondition & _
  24.     strPFICondition & strPOCondition & strCustRefCondition & strAcceptableCondition & _
  25.     strCauseCondition & strCategoryCondition & strSubCategoryCondition & _
  26.     strProductDestinationCondition & ");"
  27. Case False
  28.     Me.ToggleApplyFilter.Caption = "Apply filter"
  29.     strSQL = "SELECT CustomerComplaintForm.* FROM CustomerComplaintForm;"
  30.     Me.cboAcceptableFilter.value = ""
  31.     Me.cboIdFilter.value = ""
  32.     Me.cboCustomerFilter.value = ""
  33.     Me.cboCustRefFilter.value = ""
  34.     Me.cboPFIFilter.value = ""
  35.     Me.cboPOFilter.value = ""
  36.     Me.cboUnitsFilter.value = ""
  37.     Me.cboCauseFilter.value = ""
  38.     Me.cboCategoryFilter = ""
  39.     Me.cboSubCategoryFilter = ""
  40. End Select
  41.  
  42. Debug.Print strSQL
  43. Me.RecordSource = strSQL
  44.  
  45.  

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
Expand|Select|Wrap|Line Numbers
  1.  Like '*' 
for all fields for which no criteria was provided in the forms. But the result is the same - wrong. When I deleted this
Expand|Select|Wrap|Line Numbers
  1.  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
Expand|Select|Wrap|Line Numbers
  1.  Like '*' 
any more.
And it works.

It's nice it works, but I want to understand why this access behaviour?
Jan 22 '08 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
Have a look at this tutorial and see if it helps ...

http://www.thescripts.com/forum/thread762181.html
Feb 4 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.