Build report sql using five list boxes- too many options?

[glgcag]

I am building a form that will serve as the source for a report I have written. On the form I have date range options and five multiselect list boxes from which user can select criteria for a report.

Above each list box is a combo box that has a value list populated with all of the fields available for criteria. When the user selects the primary criteria, the secondary criteria box value list is populated with the available fields minus the primary selection and so on through all five list boxes. Easy enough . . .

After the combo box is updated it creates a source for the list box and requeries it. After the primary criteria, I want the list boxes to list only the items as they correspond to the previous list box. Here's an example:

Primary criteria: Client
Clients selected: 462, 559
Secondary criteria: Project
Now in the secondary criteria list box I only want projects that clients 462 and 559 are on. Easy enough . . .

But in the primary criteria combo box I have the possibility of 5 separate criteria selections which makes for serious amounts of possible sql statements when I select a secondary criteria, third criteria, etc. from the remaining list boxes. Is there a way to dynamically create the sql statements based on the selections or will I have to code every possible combination?

 

[psemianonymous]

For every criterion, add a string like
"(<CRITERION GOES HERE>) AND "


Do direct string concatenation so that if you have five of them selected, it should look like:

"(<CRITERION GOES HERE>) AND " & "(<CRITERION GOES HERE>) AND " & "(<CRITERION GOES HERE>) AND " & "(<CRITERION GOES HERE>) AND " & "(<CRITERION GOES HERE>) AND "

You can have it "not add" one of these criteria if it's not selected. Once you have your string built, remove the " AND " at the end of the string. Append the remaining monstrosity to the WHERE clause of the SQL statement; also you can directly apply it to the Filter property of a report, which has the same effect.



There's really no pretty way to do this, period. Good luck.

 

[MajP]

I am not sure if this is what your asking. I understood it to mean you start with 5 criteria, the next list has 4 (minus the first), the next three (minus first and second selectd), etc.
If that is the case you could build a string, where you add each criteria selected.

strSelected = strSelected & new value
ex.
"criteria1"
"criteria 1,criteria 2"

then your sql is something like

SELECT tblCriteria.strCriteria
FROM tblCriteria
WHERE ((tblCriteria.strCriteria) Not In (strSelected));

The strSelected in the query is only representative of the value, not suggesting you can put a variable in there. You could use a function to return the string or a reference to a hidden control.

 

[MajP]

Correction. My example should read
"criteria 1"
"criteria 1","criteria 2"
"criteria 1","criteria 2", "criteria 3"
etc.

 

[glgcag]

pseale,

The where clause isn't a problem, I have the code in place for that (using IN(criteria) clause for multiple selections), but it's the actual sql statement that will change with different criteria selections that I'm trying to simplify. Here's an example:

Select Case me.cmbSecondaryCriteria
Case "Project"
   Select Case me.cmbPrimaryCriteria
   Case "Client"
      sSQL = Select fields from tables based on primary and secondary criteria
   Case "Vendor
      sSQL = select via prime & second criteria
   Case "Royalty Type"
      sSQL = select via prime & second criteria
   Case "Account Number"
      sSQL = select via prime & second criteria
   End Select
Case "Vendor"
    Select Case me.cmbPrimaryCriteria
   Case "Client"
      sSQL = Select fields from tables based on primary and secondary criteria
   Case "Royalty Type"
      sSQL = select via prime & second criteria
   Case "Account Number"
      sSQL = select via prime & second criteria
   Case "Project"
      sSQL = select via prime & second criteria
   End Select
Case "Royalty Type"
etc.

Effectively, I have to nest these select cases for every criteria up to the fifth criteria multiselect list box. Is there an easier way of doing this?

 

[Remou]

Hi
What is the difference between these queries? Could the information be built into the combo? For example, if the difference was the table name, instead of:
Case "Project"
Select Case me.cmbPrimaryCriteria
You would have something like:
strSQL= "Select * From " & me.cmbPrimaryCriteria.Column(1) ...
Where column(1) contained a table name. Or even more information ...

 

[glgcag]

The problem is that me.cmbPrimary and me.cmbSecondary have many different potentional combinations that can be selected. (ie: Prime=Client, Second=Project, etc.) For each different combination I have to create a select case within a select case to make sure the sql statement is proper for that particular combination. MajP's example of creating a variable is kind of what I'm looking for, but it doesn't seem to account for the joins needed in the sql statement.

 

[psemianonymous]

Uh, if (1) your dataset is the same, and by this I mean specifically that you're basically filtering all this data off of the SAME BASE QUERY...

..and if (2) you can easily determine what datatype each field is,


...if both (1) and (2), you can do filtering. Basically make your comboboxes 3 columns deep: 1 column for the fieldname, 1 column for the data type, and 1 column for the "display name", so like:

DateOfService;Date;Enter Date Of Service

for all your fields.

Then when you're building your statement, you can basically build them straight. You just 'pull the datatype and fieldname' and then you use the datatype in an IF statement, and use the supplied fieldname to build your SELECT statement.


... what I've been leaning towards recently is figuring out EXACTLY which combinations of criteria my people need, then building custom forms to open 'their report'. This is possibly messy as you have tons of forms, but it's a LOT quicker, and NO ONE is confused by it, and the only problem is in maintainability, which I pretty much throw up my hands in defeat when dealing with Access anyway.


So if you don't get what I'm saying, consider asking your customers what they need specifically and just build that. Sometimes I offer to "dump the data" into Excel and then they can parse/sort to their heart's content. All of advice is just my opinion, of course, your mileage may vary.

 

[MajP]

Okay, I think I got it. It is a three step process. The first thing is once you pick a criteria in the first the second list is populated minus the first criteria. I think that what I mentioned earlier will give you that.
Step two is to build your "where statement" from the list box. It sounds like you have a handle on that.
Step three is once you have the second list populated you then choose the second criteria. This then is defines the row source for the second list box. So in you example you picked client as the first choice and you built a where statement:

StrWhere = "where qryData.clientID = 462 or qryData.clientID = 559

Now if in the second combo you pick project the row source for the list box is something like

"Select qryData.projectID, qryData.ProjectName from qryData" & strWhere

but if you pick vendor

"Select qryData.VendorID, qryData.VendorTitle from qryData" & strWhere

If this is correct, the solution should be easy if all the fields exist in the base query "qryData". The only thing changing is that you tell which fields to return for your list box. So I would build this table.

tblPopulateComboListBox
strDisplayName (ex. Vendor, Project, Client, etc.)
strSqlSelect (ex. "Select qryData.VendorID, qryData.VendorTitle from qryData)
strFieldForWhereStatement (ex. intVendorID)

So using the "not in" we can reduce the combo list each time. If you pick "Vendor", the select statement for the row source of the list box is in "strSqlSelect", and the "strFieldForWhereStatement" can be used to expand the where statement for the third pull down.
Now the difficulty arises if you can not use a base query like my "qryData". If the recordset from which you choose which fields to display changes depending on the order of criteria, I would still follow a similar approach. I would make 5 tables for simplicity. Example for the second combo.

tblSecondChoice
strCriteriaOne
strCriteriaTwo
strSqlSelect

ex.
Client Vendor "select VendorName from somePlace"
Client Project "select projectID from a differntplace"
Project Client "select ClientName from a thirdplace

 

[glgcag]

MajP,

Yes, I cannot use one base query for the data. Each time a selection is made in the criteria combo box, I have to generate a unique sql statement for each combination. The actual sql that I send to the report *is* one query that I reuse after I gather the criteria off of the form, but the list boxes on the report are the problem.

I think I'm going to create a function for this report form that passes each of the combo box values into and that hands back the sql for the list box. Something like:

ReportListBoxFill(sPrimary, optional sSecondary, optional sThird, optional sFourth, optional sFifth) as string

Then dependent on the number of criteria selected it can construct the sql statement for each list box as the user selects from them.