Those fields are included in the query to generate the sums based on the age of each record. ie, <15, 15-20 and >20 days old.
After selections made in listboxes, I am able to generate a detail page that allows users to be able to review the individual records. They would like to have the pivot table change to show how many records are selected by age
I have tried this to reset the record
Expand|Select|Wrap|Line Numbers
- Dim stdocname As String
- Dim strwhere As String
- Dim sSql As String
- strwhere = "1=1 "
- strwhere = strwhere & ListSelections(Me.WorkByCommodity, "[category]", "'")
- strwhere = strwhere & ListSelections(Me.workbyLocation, "[Location]", "'")
- Debug.Print strwhere
- sSql = "SELECT qry_Expedites_With_Comments_1.Category, qry_Expedites_With_Comments_1.Location, Sum(IIf([expr1]<15,1,0)) AS [<15], "
- sSql = sSql & " Sum(IIf([expr1]>=15 And [expr1]<=20,1,0)) AS [15-20], Sum(IIf([Expr1]>20,1,0)) AS [>20]"
- sSql = sSql & " FROM qry_Expedites_With_Comments_1"
- sSql = sSql & " where " & strwhere
- sSql = sSql & " GROUP BY qry_Expedites_With_Comments_1.Category, qry_Expedites_With_Comments_1.Location;"
- Debug.Print sSql
- Me!sfrm_ExpeditesbyLocationchart.Form.RecordSource = sSql
Here is what the sSql showed when ran.
Expand|Select|Wrap|Line Numbers
- SELECT qry_Expedites_With_Comments_1.Category, qry_Expedites_With_Comments_1.Location, Sum(IIf([expr1]<15,1,0)) AS [<15], Sum(IIf([expr1]>=15 And [expr1]<=20,1,0)) AS [15-20], Sum(IIf([Expr1]>20,1,0)) AS [>20] FROM qry_Expedites_With_Comments_1 GROUP BY qry_Expedites_With_Comments_1.Category, qry_Expedites_With_Comments_1.Location HAVING 1=1 AND [Location] In ('MAP') ;