Filter subform by multiple textbox options (1 Viewer)

Redfang87 · Jun 20, 2017

So I have my form with a subform displaying open inspections, On the parent form I have text boxes and combo box's for user input's to filter what is shown in the subform.
I have got this working as intended through the subforms select SQL but the method really doesn't feel like it is a good way to do it and im left with not being able to have multiple filters it only reading one at a time. The SQL ive used is below, Id really appreciate some feedback to improve or alternate ways to get the desired results in filtering.

Researching subform filtering wasn't getting me successful results and working out the method has been my only success.

Code:

SELECT Inspections.INS_ID, Inspections.Category, Inspections.Assigned_Officer, Inspections.Raised_For, Inspections.Account, Inspections.Number, Inspections.Street, Inspections.Area, Inspections.Postcode, Inspections.Date_Raised, Inspections.Reason, Inspections.INS_Comments
FROM Inspections
WHERE (((Inspections.Status)="Open") AND 
(
([Forms]![Manage_Open]![Filter_ID]) Is Null)
AND (([Forms]![Manage_Open]![Filter_account]) Is Null)
AND (([Forms]![Manage_Open]![Filter_officer]) Is Null)
AND (([Forms]![Manage_Open]![Filter_Number]) Is Null)
AND (([Forms]![Manage_Open]![Filter_Postcode]) Is Null)
AND (([Forms]![Manage_Open]![Filter_Category]) Is Null)
AND ((([Forms]![Manage_Open]![Filter_From]) Is Null) AND  (([Forms]![Manage_Open]![Filter_To]) Is Null))
)
OR (([Forms]![Manage_Open]![Filter_ID]) Is Not Null And ([Forms]![Manage_Open]![Filter_ID])=[Inspections].[INS_ID])
OR (([Forms]![Manage_Open]![Filter_account]) Is Not Null And ([Forms]![Manage_Open]![Filter_account])=[Inspections].[Account])
OR (([Forms]![Manage_Open]![Filter_officer]) Is Not Null And ([Forms]![Manage_Open]![Filter_officer])=[Inspections].[Assigned_Officer])
OR (([Forms]![Manage_Open]![Filter_Number]) Is Not Null And ([Forms]![Manage_Open]![Filter_Number])=[Inspections].[Number])
OR (([Forms]![Manage_Open]![Filter_Postcode]) Is Not Null And ([Forms]![Manage_Open]![Filter_Postcode])=[Inspections].[Postcode])
OR (([Forms]![Manage_Open]![Filter_Category]) Is Not Null And ([Forms]![Manage_Open]![Filter_Category])=[Inspections].[Category])
OR (((([Forms]![Manage_Open]![Filter_From]) Is NOT Null) AND  (([Forms]![Manage_Open]![Filter_To]) Is NOT Null)) And ([Inspections].[Raised_For]) BETWEEN (([Forms]![Manage_Open]![Filter_From]) AND ([Forms]![Manage_Open]![Filter_to]) ))
;

Redfang87 · Jun 20, 2017

To update my question, Im happy with the results im getting from this, if there is a better way great im all ears for trying other subform user filter methods, What I do need thought is for it to accept multiple filters without having to write out every possible combination in the above code.

effectively I need the

Code:

OR ((([Forms]![Manage_Open]![Filter_ID]) Is Not Null And ([Forms]![Manage_Open]![Filter_ID])=[Inspections].[INS_ID])
AND/OR (([Forms]![Manage_Open]![Filter_account]) Is Not Null And ([Forms]![Manage_Open]![Filter_account])=[Inspections].[Account])
AND/OR (([Forms]![Manage_Open]![Filter_officer]) Is Not Null And ([Forms]![Manage_Open]![Filter_officer])=[Inspections].[Assigned_Officer])
AND/OR (([Forms]![Manage_Open]![Filter_Number]) Is Not Null And ([Forms]![Manage_Open]![Filter_Number])=[Inspections].[Number])
AND/OR (([Forms]![Manage_Open]![Filter_Postcode]) Is Not Null And ([Forms]![Manage_Open]![Filter_Postcode])=[Inspections].[Postcode])
AND/OR (([Forms]![Manage_Open]![Filter_Category]) Is Not Null And ([Forms]![Manage_Open]![Filter_Category])=[Inspections].[Category])
AND/OR (((([Forms]![Manage_Open]![Filter_From]) Is NOT Null) AND (([Forms]![Manage_Open]![Filter_To]) Is NOT Null)) And ([Inspections].[Raised_For]) BETWEEN (([Forms]![Manage_Open]![Filter_From]) AND ([Forms]![Manage_Open]![Filter_to]) ))

so is there anyway to easily express AND/OR

Where [STATUS] = "Open"

AND (if any form fields are not null filter the subform to all those that are not null )

bentheimmigrant · Jun 23, 2017

Redfang87 said:
so is there anyway to easily express AND/OR

Unless I'm misunderstanding what you mean (entirely possible!), AND/OR is expressed as OR.

As for your overall question, you could write some VBA to go through your multiple textboxes, and build the query up. So if there is something in each box, its filter gets added to the string.

Filter subform by multiple textbox options (1 Viewer)

Redfang87

New member

Redfang87

New member

bentheimmigrant

Lost & confused

Similar threads

Users who are viewing this thread