MS ACCESS: How to filter report using combo box and query criteria

Question

I have a database that has 3 tables (ListOfDevice, DeviceType, Employee).

ListOfDevice Table

1. DeviceName
2. Device Model
3. DeviceType ------> Referenced to the DeviceType table using ID and DeviceType.
4. EmployeeID ------> Referenced to the Employee table using ID and FirstName.
5. DeviceStatus ------> This only have two values either Working or Damaged.

Some of the record does not have employeeID, and those are labelled "Damaged" on the device status field.

I created a query and named it as "QryDeviceLists" and inside this query I imported those 3 tables and begin filtering for the query and report result. I used the "Like" and Wildcard for filtering.

Example: Like"*" &[Form]![FormName]![ControlName or Combo box name]&"*"

What I'm trying to filter here is the DeviceType, EmployeeID and the DeviceStatus. And the way I want this filter to work is through combo boxes, I have a form which I named as filter and inside this form is 3 unbound combo boxes linked to the those 3 tables. And here is how I will generate a report:

• If I select a value from the devicetype combobox either "Desktop or laptop" and leave the other combo boxes blank then, it should give me a result of either laptop or desktop with all the users and the device status whether working or damaged.

• The same for the EmployeeID combo box, if I selected a name or employee ID it should give a result of the specific device assigned specifically for that employee.

• And for the device status combo box, it should give a result either working or damaged depending on the selection from the combo box.

• And by leaving all the combo boxes blanks it should show all the record of the devices whether it's a laptop or desktop, working or damaged, assigned to all employees.

Here are the problems which I'm facing when trying to pull out a report using the combo boxes.

• If I select a value from the Device Type and leave the employee ID and Status as blank, no records are showing. Same problem goes to Employee ID Combo Box, If I leave the Device Type and Status as blank, also no records are showing.

• If I leave all the combo boxes blanks then only the records which are labelled as Working on the Device Status are showing. This is maybe because the other record which is labelled as Damaged has no value on the employee ID field. Because when I tried to put a value inside the Employee ID field and regenerate the query by leaving all the combo boxes empty then all the records show up.

Note: I don't have any VBA experience, and I'm using Access 2013-2016

Here are the query SQL Lines SELECT ListOfDevices.ID, ListOfDevices.DeviceName, ListOfDevices.[Device Model], DeviceType.DeviceType, Employee.FirstName, ListOfDevices.DeviceStatus FROM Employee RIGHT JOIN (DeviceType RIGHT JOIN ListOfDevices ON DeviceType.ID = ListOfDevices.DeviceType) ON Employee.ID = ListOfDevices.EmployeeID WHERE (((DeviceType.DeviceType) Like "*" & [Forms]![filter]![CboDevType] & "*") AND ((Employee.FirstName) Like "*" & [Forms]![filter]![CboEmp] & "*") AND ((ListOfDevices.DeviceStatus) Like "*" & [Forms]![filter]![CboStat] & "*"));

Answer 1

Consider this pure SQL solution with WHERE clauses pointing to comboboxes (no LIKE expressions). And if comboboxes are empty, NZ() returns the field itself. No VBA needed if report/form is bound to this query.

QryDeviceLists

SELECT l.ID, l.DeviceName, l.[Device Model], d.DeviceType, e.FirstName, l.DeviceStatus 
FROM Employee e
RIGHT JOIN (DeviceType d
RIGHT JOIN ListOfDevices l ON d.ID = l.DeviceType) 
   ON e.ID = l.EmployeeID 

WHERE ((d.DeviceType) = NZ([Forms]![filter]![CboDevType], d.DeviceType)
   AND (e.ID) = NZ([Forms]![filter]![CboEmp], e.FirstName)
   AND (l.DeviceStatus) = NZ([Forms]![filter]![CboStat], l.DeviceStatus))

    OR     
    (
        IIF([Forms]![filter]![CboDevType] IS NULL, d.DeviceType IS NULL, [Forms]![filter]![CboDevType] = d.DeviceType)
    AND IIF([Forms]![filter]![CboEmp] IS NULL, e.ID IS NULL, [Forms]![filter]![CboEmp] = e.ID)
    AND IIF([Forms]![filter]![CboStat] IS NULL, l.DeviceStatus IS NULL, [Forms]![filter]![CboStat] = l.DeviceStatus)
    )

    OR    
    (
      IIF([Forms]![filter]![CboDevType] IS NULL AND 
          [Forms]![filter]![CboEmp] IS NULL AND
          [Forms]![filter]![CboStat] IS NULL, 

          d.DeviceType IS NULL OR e.ID IS NULL OR l.DeviceStatus IS NULL , 

          [Forms]![filter]![CboDevType] = d.DeviceType OR
          [Forms]![filter]![CboEmp] = e.ID OR
          [Forms]![filter]![CboStat] = l.DeviceStatus)         
    )

Notes:

• Depending on your setup, possibly a Requery call on form or report is needed (available as a macro command)

• Include a Remove Filter button on your form that uses the SetValue macro event and assigns to all controls the value NULL.

Answer 2

I found a solution from other forum which I can't mentioned it because I don't if it's allowed here. However, here is the SQL Line which I use to solved the issue.

SELECT ListOfDevices.ID, ListOfDevices.DeviceName, ListOfDevices.[Device Model], DeviceType.DeviceType, Employee.FirstName, ListOfDevices.DeviceStatus FROM Employee RIGHT JOIN (DeviceType RIGHT JOIN ListOfDevices ON DeviceType.ID = ListOfDevices.DeviceType) ON Employee.ID = ListOfDevices.EmployeeID WHERE ((([DeviceType.DeviceType] Like "*" & [Forms]![filter]![CboDevType] & "*" Or [Forms]![filter]![CboDevType] Is Null)=True) AND (([FirstName]=[Forms]![filter]![CboEmp] Or [Forms]![filter]![CboEmp] Is Null)=True) AND (([DeviceStatus]=[Forms]![filter]![CboStat] Or [Forms]![filter]![CboStat] Is Null)=True));

Thank you Mr. Parfait for your remarkable effort.