filters in continuos form using VBA code

[TriFith]

Hello,
I would like to ask you for advice with filtering my data in continuous form, using VBA code.
I have a list of data collected from several tables:



At the top of the list there are fields used for filtering. I have set "AfterUpdate" action in VBA for every filter filed to set filter:

DoCmd.ApplyFilter , "Done = FALSE And JdeWo LIKE '*" & fWO & "*' AND AsmCase LIKE '*" & Fcase & "*' AND tblProformaType.Type LIKE '*" & fProf & "*' AND tblOrderType.Type LIKE '*" & fOrder & "*' AND tblPartsType.Type LIKE '*" & fParts & "*' AND tblPlanningType.Type LIKE '*" & fPlan & "*' AND tblWarranty.Type LIKE '*" & fWarr & "*' AND Customer LIKE '*" & fCust & "*' "

Filtering is working pretty well, but only if all filtered columns are filled in correctly. If some of filtered field is empty, whole record is excluded from filter.

Here is an example:
1) no filter selected:

- two records for customer _dev_test are shown (one of them has all fields filled in, second record has no data filled in field AsmCase

2) filter for column Customer is applied:

- only one record is selected (record with all fields filled in).


Can you please advice me, how I can modify my VBA code to filter data including empty columns?

Thank you very much

Regards TriFith

[ranman256]

Check every cbo box and then build the filter....

sub cboBox1_afterupdate()
  FilterData
end sub

sub cboBox2_afterupdate()
  FilterData
end sub

sub FilterData()
Dim sWhere as string

if cboBox1<>"*" then sWhere = sWhere & " and [field1]='" & cboBox1 & "'"
if cboBox2<>"*" then sWhere = sWhere & " and [field2]='" & cboBox2 & "'"

if sWhere ="" then
    Me.filterOn= false
 else
    'Remove 1st AND
    SWhere = mid(sWhere,5)
    Me.filter = sWhere
    Me.filterOn= true
end if
end sub

[CJ_London]

you can try using the nz function

....AND nz(tblProformaType.Type) LIKE '*" & fProf & "*' AND....

on a separate note, Type is a reserved word, using it as a field name will cause you a problem at some point

[TriFith]

Check every cbo box and then build the filter....

sub cboBox1_afterupdate()
  FilterData
end sub

sub cboBox2_afterupdate()
  FilterData
end sub

sub FilterData()
Dim sWhere as string

if cboBox1<>"*" then sWhere = sWhere & " and [field1]='" & cboBox1 & "'"
if cboBox2<>"*" then sWhere = sWhere & " and [field2]='" & cboBox2 & "'"

if sWhere ="" then
    Me.filterOn= false
 else
    'Remove 1st AND
    SWhere = mid(sWhere,5)
    Me.filter = sWhere
    Me.filterOn= true
end if
end sub

Thank you for answer.
I am not sure if I understand it correctly, but code what you wrote will avoid filtering with empty comboboxes...??? Is that correct?
Because that is not my problem... My problem is in filtered data.


I have an idea how to solve this, but I still can´t find way how to set filter for empty fields...
For example If I have columnnamed AsmCase and I would like to filter all records with no value in this column... What should be the syntax of this VBA filter command??

[TriFith]

Och...
I have finally find out how to solve this...
When I understood syntax of handling with NULL value, solution was simple

Me.Filter = " Customer LIKE '*" & fCust & "*' AND (([AsmCase] Is Null) OR (AsmCase LIKE '*" & Fcase & "*'))........"

[CJ_London]

happy to help!