Listbox Display Aggregate and allow pieces to feed query

[John3Chr]

Listbox on form - desire these selections below:

2016A(Base)
2016B(Adj)
2016C(2016A(Base)+2016B(Adj))
2017D(Base)
2017E(Adj)
2017F(2017D(Base)+2017E(Adj))
2017G(Base)
2017H(Adj)
2017I(2017G(Base)+2017H(Adj))



How do I get code to build query to recognize 2016C, 2017F and 2017I and feed query as cumulative? Currently query works if I choose 2016A or 2016B or 2016A and 2016B together.
Results are In('2016A') or In('2016B') or In ('2016A','2016B'). I want to be able to display 2016C in list box and the results when selected would be as if both 2016A and 2016B were
selected ie. results in strCriteria would be In('2016A','2016B'). Also, I would like 2017F and 2017I to give cumulative results and display 2017F and 2017I in listbox to be available
for selection. I want the flexibility to use this for 2018 and 2019 because scenario will be the same.

Code is below:
Private Sub cmd_filter_timeframe_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim qdf2 As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qry_actTimeframe")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!lst_act_timeframe.ItemsSelected
strCriteria = strCriteria & ",'" & Me!lst_act_timeframe.ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub

End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
strSQL = "SELECT tbl_A_CFS_Scenario.act_Timeframe FROM tbl_A_CFS_Scenario " & _
"WHERE tbl_A_CFS_Scenario.act_Timeframe IN(" & strCriteria & ");"


' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

[ranman256]

Can you not pick '2016' in the listbox,and have it pull all records: 2016*

[John3Chr]

I don't think so, right?. I want to give the aggregate in 2016 a name like 2016C. We couldn't do 2017* but named 2017F and 2017I keeps them unique.

[aytee111]

Get the query working in query design.

Begin with making a simple selection on your form
- run the query from the nav pane - if it is not producing the records you desire, open it in design view and fix it until is does
- change the VBA to match
- add another selection on the form
- repeat