Using Combo Box to filter a form query - iif statement is too long

[MattSC]

Hi, I have a form where I'm using a combo box as a filter to pass multiple filter criteria based on the combo box selection. I created a field in the form's query that is using the combobox value in an if statement (bolded below) to filter the form. The problem is that my code for the "Filter" field has gotten so long that it is getting truncated automatically by access. Is there a different/better way to accomplish the filtering of my form (possibly putting the combo box values into a table)? I have limited experience with VBA, but willing to give it a shot if necessary. Here's the SQL-

SELECT Initiatives.InitiativeID, Initiatives.Initiative, Initiatives.AOR, Initiatives.MemberName, Milestones.Comments, Milestones.Deliverable, Initiatives.TechnicallyCompleteDate, Milestones.ReportType, Milestones.DueDate, Milestones.ReceivedDate, IIf([Combo425] Is Null,1,IIf([Combo425]="Not Submitted" And [ReportInitialReceipt] Is Null,1,IIf([Combo425]="Rev to Draft" And (([ProgramIssues] Is Not Null And [ProgramIssueResolved] is Null) Or ([ContractIssues] Is Not Null And [ContractIssueResolved] is Null)) And [RevisionRequestDrafted] Is Null,1,IIf([Combo425]="Rev Req to Send" And [RevisionRequestDrafted] Is Not Null And [RevisionRequestSent] Is Null,1,IIf([Combo425]="Need to Send to AOR" And [ReportInitialReceipt] Is Not Null And [ReportSentToAOR] Is Null,1,IIf([Combo425]="Awtg Program Rvw" And [ReportInitialReceipt] Is Not Null And ([ProgramIssueResolved] Is Null Or [ProgramIssues] Is Null),1,IIf([Combo425]="Awtg Contract Rvw" And [ReportInitialReceipt] Is Not Null And ([ContractIssueResolved] Is Null Or [ContractIssues] Is Null),1,IIf([Combo425]="Awtg Rev" And [RevisionReceived] Is Null And [RevisionRequestSent] Is Not Null,1,IIf([Combo425]="Acceptable" And ([ProgramIssueResolved] Is Not Null And [ContractIssueResolved] Is Not Null Or [ReportAcceptable] Is Not Null),1,2))))))))) AS Filter, Milestones.ReportInitialReceipt, Milestones.ReportSentToAOR, Milestones.ReportComments, Milestones.ProgramIssues, Milestones.ProgramIssueResolved, Milestones.ContractIssues, Milestones.ContractIssueResolved, Milestones.RevisionRequestDrafted, Milestones.RevisionRequestSent, Milestones.RevisionReceived, Milestones.ReportAcceptable


FROM Initiatives LEFT JOIN Milestones ON Initiatives.InitiativeID = Milestones.InitiativeID
WHERE (((Initiatives.TechnicallyCompleteDate) Is Null) AND ((Milestones.ReportType)="quarterly" Or (Milestones.ReportType)="annual" Or (Milestones.ReportType)="Unsch. Annual" Or (Milestones.ReportType)="Unsch. Qrtly") AND ((Milestones.DueDate) Between [Forms]![QuarterlyReportsWorking]![DueDateBegin] And [Forms]![QuarterlyReportsWorking]![DueDateEnd]) AND ((IIf([Combo425] Is Null,1,IIf([Combo425]="Not Submitted" And [ReportInitialReceipt] Is Null,1,IIf([Combo425]="Rev to Draft" And (([ProgramIssues] Is Not Null And [ProgramIssueResolved] is Null) Or ([ContractIssues] Is Not Null And [ContractIssueResolved] is Null)) And [RevisionRequestDrafted] Is Null,1,IIf([Combo425]="Rev Req to Send" And [RevisionRequestDrafted] Is Not Null And [RevisionRequestSent] Is Null,1,IIf([Combo425]="Need to Send to AOR" And [ReportInitialReceipt] Is Not Null And [ReportSentToAOR] Is Null,1,IIf([Combo425]="Awtg Program Rvw" And [ReportInitialReceipt] Is Not Null And ([ProgramIssueResolved] Is Null Or [ProgramIssues] Is Null),1,IIf([Combo425]="Awtg Contract Rvw" And [ReportInitialReceipt] Is Not Null And ([ContractIssueResolved] Is Null Or [ContractIssues] Is Null),1,IIf([Combo425]="Awtg Rev" And [RevisionReceived] Is Null And [RevisionRequestSent] Is Not Null,1,IIf([Combo425]="Acceptable" And ([ProgramIssueResolved] Is Not Null And [ContractIssueResolved] Is Not Null Or [ReportAcceptable] Is Not Null),1,2)))))))))=1) AND ((Milestones.ReportInitialReceipt)<=[TechnicallyCompleteDate]));

Thank you in advance for your help!! Matt

[orange]

Matt, just an observation ---Too much code to review.

We only know what you tell us and you've told us nothing about your application. What is the database about?
Did you Normalize your tables? Can you show us a jpg of your tables and relations?
There may be options to improve your set up, but they will only come once readers have a better understanding of what you're trying to do.
But, I would venture to say there are better ways to do this.

Just out of curiosity, did you
a) draw a logic chart/diagram of what the criteria should be, and walk through it with sample data
b)work through simple queries with criteria, and get them working; then add more criteria ans test; repeat this until you got to this one?

[MattSC]

Hi, thanks for the response! Sorry, I didn't realize that the table structure was relevant. Really, I'm just looking for a way to filter multiple fields of a datasheet form based on a selection from a combo box.

Here's a screenshot of the form with the combo box (being utilized as a filter). The combo box has an after update event that refreshes the subform (datasheet):



Here's a screenshot of the datasheet subform's query with a zoom on the created "Filter" field that is using the form's combo box with IIF statements to set criteria on the milestone table:



The initiatives table is a one to many relationship with the milestones table. This form is used to manage a process of receiving reports (which are a type of Milestone) from customers on projects (or Initiatives).

Yes, the form itself worked until I added another line of code to the filter field in the query. The error states that "The string returned by the builder was too long. The result will be truncated." So, now I'm trying to figure out how to get around this. Is there a better way to allow a user to select a filter from a combo box on the form that will set criteria on multiple fields (in the milestones table) once the selection is made? Data will need to be edited in the milestone table from the subform.

Here's the field in the underlying query that creates the filter. Basically, its just multiple if statements that includes the combo box selections to generate the desired criteria settings for the query:

Filter: IIf([Combo425] Is Null,1,IIf([Combo425]="Not Submitted" And [ReportInitialReceipt] Is Null,1,IIf([Combo425]="Rev to Draft" And (([ProgramIssues] Is Not Null And [ProgramIssueResolved] is Null) Or ([ContractIssues] Is Not Null And [ContractIssueResolved] is Null)) And [RevisionRequestDrafted] Is Null,1,IIf([Combo425]="Rev Req to Send" And [RevisionRequestDrafted] Is Not Null And [RevisionRequestSent] Is Null,1,IIf([Combo425]="Need to Send to AOR" And [ReportInitialReceipt] Is Not Null And [ReportSentToAOR] Is Null,1,IIf([Combo425]="Awtg Program Rvw" And [ReportInitialReceipt] Is Not Null And ([ProgramIssueResolved] Is Null Or [ProgramIssues] Is Null),1,IIf([Combo425]="Awtg Contract Rvw" And [ReportInitialReceipt] Is Not Null And ([ContractIssueResolved] Is Null Or [ContractIssues] Is Null),1,IIf([Combo425]="Awtg Rev" And [RevisionReceived] Is Null And [RevisionRequestSent] Is Not Null,1,IIf([Combo425]="Acceptable" And ([ProgramIssueResolved] Is Not Null And [ContractIssueResolved] Is Not Null Or [ReportAcceptable] Is Not Null),1,2)))))))))

I hope this makes sense, please let me know if more info is needed.

[June7]

Is that SQL a saved query object or is it directly in the form RecordSource?

An alternative is not to use dynamic parameters in the query but to instead use VBA to construct WHERE clause and set the Filter and FilterOn properties of form.

That's a really long and complicated filter criteria.

[orange]

I agree with June7, it's long and complicated and very suited to constructing with vba.
I'd like to see the tables jpg with the tables extended to show all of the fields.
I'm concerned a little re Normalized tables, also
I see various "constants" in combo425
"Not Submitted"
"Rev to Draft"
"Rev Req to Send"
"Need to Send to AOR"
"Awtg Program Rvw"
"Awtg Contract Rvw"
"Awtg Rev"
"Acceptable"

These suggest a Status /Condition Table. I would also think that your Milestones table is hiding one or more entities.
But you know your data and business better than we do.

[MattSC]

The SQL is directly from the form recordsource.

I've done some simple vba stuff and understand it to a degree, but I don't know how to place the where clause in vba. I've found a few threads on using tables to store the selections and don't know if I could store the combo box selections and code as separate fields in a table and then somehow access these fields to set the criteria of the query?? I'm open to any option that will allow me to get the combo box filter working again.

[MattSC]

Sorry, after the truncating issue, we starting trying to make the combo box selections shorter in the code. Here's the If statement with matching combo box selection names:

Filter: IIf([Combo425] Is Null,1,IIf([Combo425]="Not Submitted" And [ReportInitialReceipt] Is Null,1,IIf([Combo425]="Revisions to Draft" And (([ProgramIssues] Is Not Null And [ProgramIssueResolved] is Null) Or ([ContractIssues] Is Not Null And [ContractIssueResolved] is Null)) And [RevisionRequestDrafted] Is Null,1,IIf([Combo425]="Revision Requests to Send" And [RevisionRequestDrafted] Is Not Null And [RevisionRequestSent] Is Null,1,IIf([Combo425]="Need to Send to AOR" And [ReportInitialReceipt] Is Not Null And [ReportSentToAOR] Is Null,1,IIf([Combo425]="Awtg Program Review" And [ReportInitialReceipt] Is Not Null And ([ProgramIssueResolved] Is Null Or [ProgramIssues] Is Null),1,IIf([Combo425]="Awtg Contract Review" And [ReportInitialReceipt] Is Not Null And ([ContractIssueResolved] Is Null Or [ContractIssues] Is Null),1,IIf([Combo425]="Awtg Revision" And [RevisionReceived] Is Null And [RevisionRequestSent] Is Not Null,1,IIf([Combo425]="Acceptable Reports" And ([ProgramIssueResolved] Is Not Null And [ContractIssueResolved] Is Not Null Or [ReportAcceptable] Is Not Null),1,2)))))))))

Here's the query view of tables with all applicable fields:

[orange]

You should not resort to shortening your messages in order to make an SQL length limit. That is a flag.

I think you may have some hidden "things" in your Milestones table. It seems to me there would/could be different Milestones for different types of initiatives, or initiatives at different stages in their life cycle.

[MattSC]

Yes, it was definitely a flag, was trying to use the short way around, but fixing the problem for long-term will be much better.

Those are all of the fields in the Milestones table. We use the report type field to categorize if milestone report type, otherwise we are unconcerned with the milestone type.

There isn't a status/condition table, these are just in the row source (value list) of the combo425 combo box.

[June7]

Example code to build filter criteria and set Filter property http://www.allenbrowne.com/ser-62code.html

[qa12dx]

how i'd do it;
when making tables, use shorter names, for eg. "TechnicallyCompleteDate", i'd use "techcompdt" at most. i preffer to keep it shorter tho like "tcdt". use the desc field in the table to write note for refference. the desc shows up in mouse over in design view of a form.
control name; Combo425 shortened to cbo1

the following;
(Milestones.ReportType)="quarterly" Or (Milestones.ReportType)="annual" Or (Milestones.ReportType)="Unsch. Annual" Or (Milestones.ReportType)="Unsch. Qrtly")

can be written as;
(Milestones.ReportType)in("quarterly","annual","Un sch. Annual","Unsch. Qrtly")

i try and make a query without any criterias so i can use the same query in many forms. i filter the query based on my form needs.

when i run into a lot of conditions, i use a text field, that i hide(visible=no), to hold a value, setting a default value is a good idea for the text box.
then i place a button. the button has a conditional macro;
condition 1 - setvalue; textbox=1
condition 2 - setvalue; textbox=2
condition 3 - setvalue; textbox=3
condition 4 - setvalue; textbox=4
condition 5 - setvalue; textbox=5
condition 6 - setvalue; textbox=6
condition [textbox]=1 - openquery qry1 where .....
condition [textbox]=2 - openquery qry1 where .....

[MattSC]

Example code to build filter criteria and set Filter property http://www.allenbrowne.com/ser-62code.html

Thanks for the link June! I'm using the code provided in the link and adding my long if statement section by section while testing. I'll reply once I've gotten it working or if I run into a snag.

Thanks again and to all that provided replies/help!

[MattSC]

Solved by the above link from June7. Thanks again!

[BlueIshDan]

This should generate a form for you to go by
https://www.accessforums.net/code-re...orm-44282.html