AutomateMyOffice
asked on
Complex filtering by Access form, building SQL statement in code
I need to filter project records in Microsoft Access according to criteria determined by selections made on a form, using about 60 checkboxes as well as about 5 combo boxes and 5 text fields. I thought maybe Access's Filter-by-Forms would work, but it turns out I need to be able to handle much more complex filtering than that feature is capable of -- although I need it to work in a similar user-friendly manner. The criteria would be a mix of MUST MEET and INCLUDE IF PRESENT values. Note that this is for a customer with users that would be totally incapable of building queries or filters any other way.
An example would be to retrieve projects where ProjectManager was "Joe Expert" (combo box) and CompletionDate was "after 12/31/2016" (text box). Of those projects, I would only be interested in those where crafts included "Electrical" work (check box) or "Plumbing" work (check box) or with "Schools" as a vertical market (check box). So the first two would be MUST MEETs and the last three would be INCLUDE IF PRESENTs.
The approach I am considering would be to first allow the MUST MEETs to be designated by the user by having, for instance, their associated label's background color set to green by a double-click event. Any control without a green label would be an INCLUDE IF PRESENT. When the criteria were all selected on the form, the user would hit a button which would first (in VBA) loop through and examine all the form's controls and store references to the MUST MEETs in one array and the INCLUDE IF PRESENTs in another. When that is complete, a query would then be constructed (based on the contents of the arrays) with the appropriate logical operators in the WHERE clause, something like this pseudo-SQL (using the example):
WHERE ProjectMgr = "Joe Expert" AND CompDate > "12/31/2016" AND (ElectricalCraft = True OR PlumbingCraft = True OR SchoolVrtMkt = True)
I would then use the resultant SQL to generate a report listing the projects that meet all designated criteria.
Is this a sound approach, or can you suggest a better or simpler or more elegant solution? Thanks!
An example would be to retrieve projects where ProjectManager was "Joe Expert" (combo box) and CompletionDate was "after 12/31/2016" (text box). Of those projects, I would only be interested in those where crafts included "Electrical" work (check box) or "Plumbing" work (check box) or with "Schools" as a vertical market (check box). So the first two would be MUST MEETs and the last three would be INCLUDE IF PRESENTs.
The approach I am considering would be to first allow the MUST MEETs to be designated by the user by having, for instance, their associated label's background color set to green by a double-click event. Any control without a green label would be an INCLUDE IF PRESENT. When the criteria were all selected on the form, the user would hit a button which would first (in VBA) loop through and examine all the form's controls and store references to the MUST MEETs in one array and the INCLUDE IF PRESENTs in another. When that is complete, a query would then be constructed (based on the contents of the arrays) with the appropriate logical operators in the WHERE clause, something like this pseudo-SQL (using the example):
WHERE ProjectMgr = "Joe Expert" AND CompDate > "12/31/2016" AND (ElectricalCraft = True OR PlumbingCraft = True OR SchoolVrtMkt = True)
I would then use the resultant SQL to generate a report listing the projects that meet all designated criteria.
Is this a sound approach, or can you suggest a better or simpler or more elegant solution? Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am, in most cases, a stickler for normalization and table-driven programming. This particular customer requires extreme ease-of-use, with a screen having columns of exposed checkboxes for all crafts, services and vertical markets. The Project Managers are unlikely to fill out the form if it's not cliick-click-click in the three groups, with all available choices on the screen at once.
I would prefer three infinite scrolling subforms, but making choices from a combo box for each craft or service or vertical market added to a project in their respective subform records would be asking too much of them. I would be very pleased to be shown how to accomplish the "screen-full of checkboxes" user interface, using three child tables linked to the master project table, if there is a way!
I would prefer three infinite scrolling subforms, but making choices from a combo box for each craft or service or vertical market added to a project in their respective subform records would be asking too much of them. I would be very pleased to be shown how to accomplish the "screen-full of checkboxes" user interface, using three child tables linked to the master project table, if there is a way!
What about multi-select listboxes? It saves you a ton of programming effort (you collect the values with a loop and use them in an In() clause) and form changes triggered by data changes (spreadsheetitis).
I'm not sure why you want to create an array of the controls which are checked, and then build your sql, I would generally do something like"
Dim varOrConditions as variant
varOrConditions = ""
if me.chkElectrical = true then
varOrConditions = ( varOrConditions + " OR ") & "([ElectricalCraft] <> 0)"
endif
if me.chkk_PlumbingCraft = True Then
varOrConditions = ( varOrConditions + " OR ") & "([PlumbingCraft] <> 0)"
endif
if me.chk_SchoolVrtMkt = True Then
varOrConditions = ( varOrConditions + " OR ") & "([SchoolVrtMkt] <> 0)"
endif
and then append that to the end of your other criteria:
strSQL = strSQL & strWhere & (" AND " + varOrConditions)
Rather than typing 60 of these statements, I would probably simply loop through all of the controls on the form and look for those which are checkboxes, then use the name of the control or maybe the tag property of the checkbox to store the actual field name associated with each control.
Dim varOrConditions as variant
varOrConditions = ""
if me.chkElectrical = true then
varOrConditions = ( varOrConditions + " OR ") & "([ElectricalCraft] <> 0)"
endif
if me.chkk_PlumbingCraft = True Then
varOrConditions = ( varOrConditions + " OR ") & "([PlumbingCraft] <> 0)"
endif
if me.chk_SchoolVrtMkt = True Then
varOrConditions = ( varOrConditions + " OR ") & "([SchoolVrtMkt] <> 0)"
endif
and then append that to the end of your other criteria:
strSQL = strSQL & strWhere & (" AND " + varOrConditions)
Rather than typing 60 of these statements, I would probably simply loop through all of the controls on the form and look for those which are checkboxes, then use the name of the control or maybe the tag property of the checkbox to store the actual field name associated with each control.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dale: As stated in my original question, my plan was indeed to loop through and examine all the form's controls (of several types) and store in an array the criteria that would contribute to building the SQL. The reason I am choosing to hold it in an array is to make it convenient to access (after creating the SQL) to build a header for the report, displaying the various criteria that were used for the filter, nicely-formatted and human-readable.
Pat; I like the idea of the multi-select listboxes connected to child tables, but it will need to wait for another project. There is too much already in place that is dependent on the current data model. I looked into this after reading your comment, and it seems OK for making selections and collecting them for an In() clause -- but maybe not so straightforward if I want to store the filter criteria, name it, and open it easily later for adjustment. Is there an easy way to, after using a listbox to select, say, B and C, out of a list A-B-C-D-E, store the selections, and then later display that list A-B-C-D-E with B and C highlighted as having been selected at some earlier date?
Pat; I like the idea of the multi-select listboxes connected to child tables, but it will need to wait for another project. There is too much already in place that is dependent on the current data model. I looked into this after reading your comment, and it seems OK for making selections and collecting them for an In() clause -- but maybe not so straightforward if I want to store the filter criteria, name it, and open it easily later for adjustment. Is there an easy way to, after using a listbox to select, say, B and C, out of a list A-B-C-D-E, store the selections, and then later display that list A-B-C-D-E with B and C highlighted as having been selected at some earlier date?
I'm pretty sure the answer is yes. Although if that is what you want, this might be easier if you use a multi-value field in the table where you save the criteria. You might be able to get away with less code although I don't ever use multi-value fields for numerous reasons so I can't say for sure. Post back if you get to the point where you want to implement the solution.
In my 50+ years of experience I have found that correcting design flaws as soon as they are discovered ultimately ends up saving time and makes for a sounder application. It seems like a lot of work but over the life of the project, not correcting the flaw results in more work and constant workarounds.
In my 50+ years of experience I have found that correcting design flaws as soon as they are discovered ultimately ends up saving time and makes for a sounder application. It seems like a lot of work but over the life of the project, not correcting the flaw results in more work and constant workarounds.
Yes, you can use VBA code to select certain items in a multi-select listbox, based on a saved filter. There's non-trivial coding involved, but it's certainly possible.
ASKER
Still open to anyone who can take on the challenge and point me in a better direction!