Filtering Query Reposrt using form and multiple combo box (1 Viewer)

[Mezta1988]

Hi experts,

I have a trouble filtering my query and report using combo boxes, I also use a wildcard on the criteria field to do the filtering.

These are the following table that I made for this specific practice.

1. Employee
-ID
-FirstName
-LastName
-Occupation

2. DeviceType
-ID
-DeviceType

3. ListOfDevices
-ID
-DeviceName
-DeviceModel
-DeviceType -----------> Referenced to the DeviceType Table
-EmployeeID ----------> Referenced to the Employee Table
-DeviceStatus

I created a form using the listOfDevice as a record source and inside the form, I add unbound combo box to store a value in the devicetype field, everything is working fine when it comes to data entry and even the records are showing in the query whenever I view the query report. So, I started putting a filter on the criteria field for the DeviceType and FirstName. On this part, the filter works fine and it gives me my desired result. However, when I add the third filter which is on the DeviceStatus field it only gives me a specific result even if I didn't select any criteria from the combo box which the DeviceStatus criteria is associated with. If I'm not mistaken, the way I understand the filtering if I leave all the criteria as blank it should show me all the record list, in other words, it will only show specific record based on the selection from the combo box.

 

[plog]

2 big, common misconceptions working against you here:

1. When you apply criteria to a field of a data source that has been LEFT JOINed, you effectively undo the LEFT JOIN and make it an INNER JOIN. So even though your JOIN says you are selecting all from ListOfDevices and only matching records in Employee, by placing criteria on Employee.FirstName you have undone that and only matching records are coming through.

2. Null does not equal "". An empty string is still something and its different than NULL. It's just one of those counter intuitive things you have to come to terms with and accept.

So, because you have a Null employeeID value in ListOfDevices and you have criteria on FirstName, the most records this query could ever return is 2 records.

My suggestion is to scrap this whole method and isntead filter your Report via DoCmd.OpenReport(https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openreport) method. First, clear out all the criteria of your query, let everything through. Second, instead of a macro firing when the form's button is clicked, use VBA to open the report. You would first build a filter string to accept the filters the user selected and then use DoCmd.Openreport to open the report to just the records that meet the criteria.

 

[Ranman256]

build the 'where' clause by cycling thru all the controls....
since you cannot search for Null, ignore these.

it executes after a find button CLICK event

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

 

[Mezta1988]

Thanks Plog and Ranman for the very informative info. it's working now.