Ignore Query Fields Depending on Form Selection

[CJ_London]

why not post your whole query - I have the feeling you are making a lot more complicated than it needs to be

 

[state90]

Ha! I guarantee that I am!

Here is the SQL if that is what you prefer to view.

SELECT REF_ACT_Expense_Current_Final.Category_Sort, REF_ACT_Expense_Current_Final.Category, DSum("[Functional_USD_Amount]","REF_ACT_Expense_Current_Final","[Month_Name] = '" & [Forms]![Frm_PARAMETER_Metrics]![CM-3Desc] & "'") AS PMLess2, DSum("[Functional_USD_Amount]","REF_ACT_Expense_Current_Final","[Month_Name] = '" & [Forms]![Frm_PARAMETER_Metrics]![CM-2Desc] & "'") AS PMLess1, DSum("[Functional_USD_Amount]","REF_ACT_Expense_Current_Final","[Month_Name] = '" & [Forms]![Frm_PARAMETER_Metrics]![CM-1Desc] & "'") AS PriorMonth, DSum("[Functional_USD_Amount]","REF_ACT_Expense_Current_Final","[Month_Name] = '" & [Forms]![Frm_PARAMETER_Metrics]![CboMonthName] & "'") AS CurrentMonth
FROM REF_ACT_Expense_Current_Final
GROUP BY REF_ACT_Expense_Current_Final.Category_Sort, REF_ACT_Expense_Current_Final.Category, REF_ACT_Expense_Current_Final.Function, REF_ACT_Expense_Current_Final.Region, 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
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]

As an update, I changed the VBA for the form button to

Private Sub Command126_Click()


Dim vFields
Dim vValues
Dim where As String
Dim i As Integer

vFields = Split("Function Country_Code Cost_Center Geography Region Budget_Region Legal_Entity")
vValues = Array(Me.CboFuncRollup, Me.Text120, Me.Text124, Me.CboGeoGroup, Me.CboRegion, Me.CboBudRegion, Me.CboLegalEntity)

For i = 0 To UBound(vFields)
If vValues(i) <> "All" Then
where = where & "AND " & vFields(i) & "='" & vValues(i) & " "
End If
Next
If Len(where) > 0 Then where = Mid(where, 5)

DoCmd.OpenReport " EXP_By_Cat_Summ", acViewReport, where
Forms("Frm_PARAMETER_Metrics").Visible = False

End Sub

 

[CJ_London]

hmm - using dsum in the way you are means you need to group them as well. Be aware that domain functions are extremely inefficient

Alternatively include the REF_ACT_Expense_Current_Final table in your main query and sum it there.

Also - I see no basis for the results of dsum being related to region, country etc.

Your next post seems to be taking you into even more unnecessary complexity. Strongly recommend you learn how tables relate to each other and how to write queries - google 'normalisation' for the former and 'sql queries' for the latter

 

[state90]

Do you recommend a good source for learning more about writing queries using SQL and VBA? No issues with how tables relate to each other but advancing beyond simply using Access windows driven "coding" for more advanced coding is out of necessity to provide more dynamic capabilities...and that is something I am learning by asking "How To" questions. Recommending a great resource would be much appreciated.

 

[CJ_London]

I've been using access for nearly 20 years and learned from books, having a particular objective in mind and 'giving it a go' (web barely existed back then) - these days there is a whole range of different teaching/reference methods available - you need to find one that suits you. Nearly all the stuff I wrote back then has been rewritten and refined many times!

I occasionally use

http://www.w3schools.com/sql/default.asp

for referencing purposes - it covers a wide variety of different flavours of sql, including Access. They don't cover vba so google 'vba tutorials' and see what you find - my guess is you will find something at the basic level, but more complex and task specific requirements (there are often many ways of achieving what you want) you will find by googling your question - you'll then find answers from many forums. 99.9% of questions have been asked and answered many times before.

It helps to learn the correct terminology, don't say fields when you mean controls or cells when you mean fields.

 

[state90]

HI CJ,

Interesting and helpful resource.

Thank you