Dynamically add fields to SQL or query based on form selection (1 Viewer)

Is it possible to add a field (i.e., variable) to a query (or SQL programming) based on a form selection?

For instance, if I use a form to allow an end-user to specify which fields they wish to include in the query (essentially creating a UI for the query builder), how would I go about creating the query/SQL or updating the query/SQL?

Thanks in advance for all assistance.

- Robbie

rahbeevt said:

Is it possible to add a field (i.e., variable) to a query (or SQL programming) based on a form selection?

For instance, if I use a form to allow an end-user to specify which fields they wish to include in the query (essentially creating a UI for the query builder), how would I go about creating the query/SQL or updating the query/SQL?

Thanks in advance for all assistance.

- Robbie

Click to expand...

One way to go about this is to use VBA to create a dynamic SQL Query that selects the required elements, and then update a dummy Query and modify its SQL Property to contain the dynamic Query. Something like the following would fit the bill. Of course you need to fill in all the missing parts.

• stQueryName is the name of the Dummy Query
• qdf is a Querydef containing the Query
• StrSelect is the Select part of the SQL Statement
• StrFrom is the From part of the SQL Statement
• StrWhere is the Where part of the SQL Statement
• rst is a recordset to use to store the results

another option would be to use DoCmd.OpenQuery once the Querydef has been updated.

-- Rookie

That is a novel idea!

Is the purpose of the subsequent (non-dummy) query to serve as an updated version of the dummy query?

I guess my specific question is, why can't I just leverage the dummy query as THE query (as opposed to creating an additional query based on the dummy query)?

rahbeevt said:

That is a novel idea!

Is the purpose of the subsequent (non-dummy) query to serve as an updated version of the dummy query?

I guess my specific question is, why can't I just leverage the dummy query as THE query (as opposed to creating an additional query based on the dummy query)?

Click to expand...

The intention is to create one Query (the Dummy Query) and makeit whatever you need it to be. Sorry for any confusion.

Dear MSAccessRookie, Any chance we can revisit this thread? What you are describing sounds exactly like what I need to figure out but I am not able to apply what you listed as a solution to my specific situation.

Here are the details that I think are relevant. I have the following:
* Criteria selection Form - [Frm_PARAMETER_Metrics]
* [Query1] - This is the query I want to summarize the data depending on the criteria selected on the form.
* Below is the VBA I currently have that runs on click of a button on the form after criteria is selected

What I want is the query to total four amount fields ([CurrentMonth], [PriorMonth]. [PMLess1] and [PMLess2]) and list these totals by one field called [Category]. There are only six categories (Personnel, Travel, etc.....) so the query results (Which feeds a report called Report1 for simplicity sake in this discussion). The rest of the fields on the form are geographic related fields that I want to be applied if selected. For example, if these geographic options are not specified then the totals will be global consolidated totals by those six categories. If "Americas" is selected for a Region then the result will be the Americas totals by these six categories. Right now, because I have those geographic variables in the query I cannot figure out how to apply these only as criteria and get 3,800 rows of results.

Private Sub Command126_Click()


Dim strCrit1 As String
Dim strCrit2 As String
Dim strCrit3 As String
Dim strCrit4 As String
Dim strCrit5 As String
Dim strCrit6 As String
Dim strCrit7 As String



strCrit1 = "[Function] "
If Me!CboFuncRollup <> "All" Then
strCrit1 = strCrit1 & "= '" & Me!CboFuncRollup & "'"
End If


strCrit2 = "[Country_Code] "
If Me!CboCountry <> "All" Then
strCrit2 = strCrit2 & "= '" & Me!Text120 & "'"
End If

strCrit3 = "[Cost_Center] "
If Me!CboCCDescription <> "All" Then
strCrit3 = strCrit3 & "= " & Me!Text124
End If

strCrit4 = "[Geography] "
If Me!CboGeoGroup <> "All" Then
strCrit4 = strCrit4 & "= '" & Me!CboGeoGroup & "'"
End If

strCrit5 = "[Region] "
If Me!CboRegion <> "All" Then
strCrit5 = strCrit5 & "= '" & Me!CboRegion & "'"
End If

strCrit6 = "[Budget_Region] "
If Me!CboBudRegion <> "All" Then
strCrit6 = strCrit6 & "= '" & Me!CboBudRegion & "'"
End If

strCrit7 = "[Legal_Entity] "
If Me!CboLegalEntity <> "All" Then
strCrit7 = strCrit7 & "= '" & Me!CboLegalEntity & "'"
End If



DoCmd.OpenReport " Query1", acViewReport, WhereCondition:=strCrit1 & " And " & strCrit2 & " And " & strCrit3 & " And " & strCrit4 & " And " & strCrit5 & " And " & strCrit6 & " And " & strCrit7
Forms("Frm_PARAMETER_Metrics").Visible = False

End Sub

Any help is much appreciated in this approach to solving my problem.

Those repeated blocks of identical functionality to me yell "LOOP." I would code that more like . . .
And see how the part in blue checks for null on the vValue? That way only controls that have data in them are included in the WHERE clause.

Does that get you closer?

Thanks Mark,

I appreciate the help. I get an error on this part of the code

DoCmd.OpenReport " Query1", acViewReport, , where

COuld it be because the query doesn't know what to do with "All" as a criteria? For example, the default on the combo boxes is "All" for aesthetic reasons/avoid confusion to the user type of thing but does the codign you provided tell the query that if a combo box = "All" all that really means is nothign was selected so it should include everything for that field? I am not explainign this well and apologize. Example, the defaulot value for Region combo box is "All". The old coding basically told the query that if no specific region was selected (i.e., the Region combo box was left at the default of "All" then [Region] is how it appeared in the where. Now it says "[Region]=All". This looks much cleaner than the coding I had. After getting through this last hurdle I just need to walk through and understand what you provided so I am learning instead of just stealing! Thank you again for your help

Yeah, oops, I wasn't paying attention to your code, but look at the part in blue again. I'm checking for Null, but you probably want to check for <> "All", so that line becomes . . .
. . . but see how easy that is to fix with this approach? You don't have 12 different expressions to change, just the one line in the loop.

Other errors: I'm missing a single quote at the end of the next line, inside the string. Also, in your DoCmd.OpenReport, the name starts with a space (" Query1"). I doubt that is a legal object name, and you'll probably find others.

But overall you see what we're doing right? We declare a list of the field names we want to use, and declare a corresponding list of controls that may hold values for those fields. Then we step thru each item in both lists in our loop, do the check (<> "All"), and conditionally add that item to our final criteria list.

I appreciate it. I do see what we are doing. I think once it is completely working as envisioned it will allow me to work my way back through the coding to understand each component and learn from it. It is definitely cleaner than how I first had it and I appreciate your help.

Here is where I stand right now.....No matter what I put in for criteria (blank or specific criteria) I end up with 3,815 rows. The way I want this report to group is on category...which means there should only be six rows. The fields that I sum will total based on the criteria selected but the group roll up by category should always be six rows so this is my next item to figure out. I am not sure if it is related to the coding we currently have....or I should say that you currently have provided for me...but I imagine I am not handling something with the query properly. I notice that the for criteria feeds right through to the report. Should it feed through to the query and then the report simply reports what flowed through to the query?

Thanks again. I will take a look at it in the morning as it is Eastern Standard here

It runs but I get no results returned and I think it is because "All" is a default value on the form in the combo boxes to let the user know that for that field, it is defaulting to pulling everything for that field. In Other words, in Region if left as "All" data for all regions will be in the result. Reading through this code it addresses what to do (or how to handle) the selections if they do not equal "All" (i.e., <> "All") but how do I make it handle if the default of "All" is in the combo boxes? When the combo boxes are left as "All" I need to include all data for that field.

Thank you

I can think of about a million reasons why this would fail silently. To troubleshoot it in any meaningful way I'd have to see the code and the SQL.