Ignore Query Fields Depending on Form Selection

[state90]

Is there a way to ignore fields depending on criteria selected in a form? I want to be able to run a summary query that totals by Category. In addition, I want to be able to select geographic criteria so that the summary query totals for the geographic selections (or consolidated). The problem I am having is because I have all the fields listed in the the Query I can't ignore the fields I don't want shown. Here is the background:

There is data that I wish to summarize by category and there are six categories so, for this rolled up view the report should total six lines (Category 1 and its total, category 2 and its total ,etc.). Let's say I select "Americas" as the region. I still want a summary total for these six categories but only if they belong to the "Americas". Instead, I am getting 3,500 rows. I know I am just missing something really simple but for the life of me, I have looked at this too long and am stuck.

Thank you in advance for any and all guidance!

 

[pbaldy]

It sounds like you want to change Group By to Where for the Region field.

 

[state90]

I tried that but couldn't get that to work. Here is my coding that opens the report and applied the criteria options from the form. Is there a way of addressing the Where clause you suggest using vba?

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

Thank you PBaldy

 

[pbaldy]

What's the SQL of the query the report is based on?

 

[state90]

Is this what you had in mind? I changed the Group By to Where in the query and under Criteria
[REF_ACT_Expense_Current_Final]![Region]=[Forms]![Frm_PARAMETER_Metrics]![CboRegion]

Thanks,

Kurt

 

[state90]

Sorry....I just saw your other question. I know there is a way to toggle to the behind the scenes SQL but I don't remember how to view that. Can I hit SHIFT and somethign to view that?

 

[pbaldy]

Yes, I meant in the query.

Does all that code work correctly? I'd build a single criteria string and add onto it when a criteria has been used. Yours will end up with the field name alone if a criteria isn't chosen. I can see where that might work, but I wouldn't do it.

 

[pbaldy]

Sorry....I just saw your other question. I know there is a way to toggle to the behind the scenes SQL but I don't remember how to view that. Can I hit SHIFT and somethign to view that?

Sounds like you already fixed it, but with the query in design view you can click on the View dropdown on the ribbon and select SQL view. You should also be able to right click on the query header.

 

[state90]

Thanks. I see it now. Here is my SQL behind the query. I cannot get this to work.

SELECT REF_ACT_Expense_Current_Final.Geography, REF_ACT_Expense_Current_Final.BudgetRegion AS Budget_Region, REF_ACT_Expense_Current_Final.Cost_Center_Num AS Cost_Center, REF_ACT_Expense_Current_Final.Country_Code, REF_ACT_Expense_Current_Final.CountryName, REF_ACT_Expense_Current_Final.Legal_Entity, REF_ACT_Expense_Current_Final.Category_Sort, REF_ACT_Expense_Current_Final.Function, REF_ACT_Expense_Current_Final.Category, Sum([REF_ACT_Expense_Current_Final]![Functional_USD_Amount]) AS PMLess2, Sum([REF_ACT_Expense_Current_Final]![Functional_USD_Amount]) AS PMLess1, Sum([REF_ACT_Expense_Current_Final]![Functional_USD_Amount]) AS PriorMonth, DSum("[Functional_USD_Amount]","REF_ACT_Expense_Current_Final","[Month_Name] = '" & [Forms]![Frm_PARAMETER_Metrics]![CboMonthName] & "'") AS CurrentMonth, Sum([REF_ACT_Expense_Current_Final]![Functional_USD_Amount]) AS CurrentMonthTEST
FROM REF_ACT_Expense_Current_Final
WHERE (((REF_ACT_Expense_Current_Final.Region)=IIf([Forms]![Frm_PARAMETER_Metrics]![CboRegion]="All",[Forms]![Frm_PARAMETER_Metrics]![CboRegion] Is Null,[Forms]![Frm_PARAMETER_Metrics]![CboRegion])))
GROUP BY REF_ACT_Expense_Current_Final.Geography, REF_ACT_Expense_Current_Final.BudgetRegion, REF_ACT_Expense_Current_Final.Cost_Center_Num, REF_ACT_Expense_Current_Final.Country_Code, REF_ACT_Expense_Current_Final.CountryName, REF_ACT_Expense_Current_Final.Legal_Entity, REF_ACT_Expense_Current_Final.Category_Sort, REF_ACT_Expense_Current_Final.Function, REF_ACT_Expense_Current_Final.Category
HAVING (((REF_ACT_Expense_Current_Final.Category_Sort)="1" Or (REF_ACT_Expense_Current_Final.Category_Sort)="2" Or (REF_ACT_Expense_Current_Final.Category_Sort)="3" Or (REF_ACT_Expense_Current_Final.Category_Sort)="4" Or (REF_ACT_Expense_Current_Final.Category_Sort)="5" Or (REF_ACT_Expense_Current_Final.Category_Sort)="6"))
ORDER BY REF_ACT_Expense_Current_Final.Category_Sort;

 

[state90]

Part of the problem is that I have the CboRegion default to "All" if nothign is selected so I figure I need to use an IIF in the Where clause of the query?

WHERE (((REF_ACT_Expense_Current_Final.Region)=IIf([Forms]![Frm_PARAMETER_Metrics]![CboRegion]="All",[Forms]![Frm_PARAMETER_Metrics]![CboRegion] Is Null,[Forms]![Frm_PARAMETER_Metrics]![CboRegion])))

It doesn't work. Now my query is looking for the [Region] value.

 

[CJ_London]

you can't create a criteria this way

try

WHERE REF_ACT_Expense_Current_Final.Region=IIf([Forms]![Frm_PARAMETER_Metrics]![CboRegion]="All",REF_ACT_Expense_Current_Final.Region,[Forms]![Frm_PARAMETER_Metrics]![CboRegion])

Basically, if cboregion is All, you want all the records so find where region=region

 

[state90]

That works but now I get an "Enter [Region]" message when I press run on the criteria form which feeds the query behind the report. It seems like now that I am using a Where clause it is searching for the Region field? Prior to adding the Where clause the code behind the run button (re-listed below) handled Region...but didn't ignore fields and gave me my original problem of wanting to be able to use various geography fields as criteria while only grouping by Category and summing the USD Amount fields.

Using my logic...and not a whole lot of coding expertise....it seems like I need to handle the Where part in the strCrit5 part below?

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

in the click button coding

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

 

[CJ_London]

your construction of your criteria is not correct

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

so StrCrit1 will either be "[Function]" or "[Function]= 'somefunction'"

so if all of your cbo's are 'All' your where clause will be

WhereCondition:="[Function] AND [Country_Code] AND ..."

 

[state90]

Hi CJ,

That is correct. What I have is a form with various geographic variables to select from and a reporting month selection that gives a time period. If none of the geographic variables are selected on the form then the result will be totals for global consolidated. If "Americas" is selected as a region then like you pointed out, the other items will be [Function] And [Country_Code] And [Cost_Center] And [Geography] And [Region] = 'Americas' And [Budget_Region] And [Legal_Entity] . That works no problem. The problem I am having is I want these geographic variables simply to be criteria. What I want is certain USD Amounts to be summed by [Category] of which there are six categories. SO, regardless of the geographic variables identified the report will always be six rows. However, I cannot figure out how to apply the geographic variables without it listing 3,800 rows because of those different variables. Here is a sample of the query results....

I want the fields from Region through Function to be determine what the totals are but not show up on the results. I only want Category (and category sort which allows me to sort in the order I want), and the summed totals for Current month, Prior Month, PMLess1 and PMLess2 (Current month totals and trailing three month totals is what those fields stand for). Once I figure out how to get this done I will fix the sum function because those are messed up too! One bridge at a time!

 

[state90]

Is there a way of uploading a pdf that shows a sample of the query results?

 

[pbaldy]

In the "Go Advanced" area you can attach a file.

 

[state90]

Thanks PBaldy!

Enclosed are two pdf files showing the results of the query behind the report. In the first, the columns whose headers are shaded in orange should not be appearing (I only printed one page to pdf to provide an example. It is actually 66 pages if I printed the whole sample). These are the variables I have on the Parameter form as criteria options to narrow down the result population that will be summed. Those columns whose headers are shaded in green are the ones that I have on the report. The problem is that even though the orange fields are not on the report the results are displaying all the records. The second pdf file enclosed shows how the report should always look regardless of the geographic variables selected as variables.

Any help is much appreciated!

 

[CJ_London]

untick the 'show' boxes for the fiends you don't want

 

[state90]

Thanks CJ,

Unfortunately, that was the setup when I first created the query. I had those fields in the query but unticked the "Show" button for those fields and was still getting 3,815 rows. The only thing that works to limit the query results is specifying the categories to include and that is built in. Those are always the case regardless of the variables selected on the parameter form. I re-ticked those fields at some point just to see the field results again.

Question....I know the form selections show up on the report filter but should these also show up in the query criteria?

 

[state90]

Could it be related to the fields that I am summing using a DSum function?

Here is what I have for the CurrentMonth field

CurrentMonth: Sum(DSum("[Functional_USD_Amount]","REF_ACT_Expense_Current_Final","[Month_Name] = '" & [Forms]![Frm_PARAMETER_Metrics]![CboMonthName] & "'"))

The gist of these fields is as follows:
* [Functional_USD_Amount] - this is the actual transaction amount field. from the query "REF_ACT_Expense_Current_Final". The Criteria field is [Month_Name] and it is pulling this criteria from the Form input.

In English, where [Month_Name] matches the Month selected on the parameter form, sum the [Functional_USD_Amount] amounts from the source query called "REF_ACT_Expense_Current_Final".