Listbox Display Aggregate and feed query it's hiearchal pieces 1

[John1Chr]

I desire listbox to display this format:

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

 

[Andrzejek]

recognize 2016C, 2017F and 2017I " - I assume you want to pick just the [blue]BLUE[/blue] part (left 5 characters) of your selected items in the list box (right?)
[tt]
2016A(Base)
2016B(Adj)[blue]
2016C[/blue](2016A(Base)+2016B(Adj))
2017D(Base)
2017E(Adj)[blue]
2017F[/blue](2017D(Base)+2017E(Adj))
2017G(Base)
2017H(Adj)[blue]
2017I[/blue](2017G(Base)+2017H(Adj))
[/tt]
If so, try:

For Each varItem In Me!lst_act_timeframe.ItemsSelected
  If Len(strCriteria) = 0 Then
    strCriteria = "'" & Left(Me!lst_act_timeframe.ItemData(varItem), 5) & "'"
  Else
    strCriteria = strCriteria & ",'" & Left(Me!lst_act_timeframe.ItemData(varItem), 5) & "'"
  End If
Next varItem

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I would create a table of groups that you can maintain a name for the group and the individual Time Frames (?) that belong in each group. Then just use the list box to display the groups. This solution would not require additional code.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[John1Chr]

Thanks, I might be overthinking this one. I wanted to display 2016C in listbox but really it is In ('2016A','2016B') and display 2017F which really is In ('2016D','2016E') and display 2017I which is really In ('2016G','2016H'). I was desiring to use these names which would be obvious to the user that it is cumulative. Wasn't sure if code could be configured easily to do this. Sounds like working with tables might be best?

 

[Andrzejek]

You lost me completely with this statement: :-(
"I wanted to [tt]
display 2016C which is really In ('2016A','2016B')
display 2017F which really is In ('2016D','2016E')
display 2017I which is really In ('2016G','2016H')[/tt]"

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[John1Chr]

C F and I are heiarchal aggregations of the Base and Adjustments. I am trying to have a Name of the Cumulative displayed that pulls in the 2 pieces of that Aggregated Total. This name would tie out to pieces underneath it. I wasn't clear, sorry.

 

[dhookom]

Again, I would add a table of group names with an autonumber primary key. Then create a junction table that links the group record with a time frame record. Add the Junction table to your query.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[John1Chr]

Duane, worked great - your suggestion..thanks!

 

[Andrzejek]

If Duane's suggestion helped your issue, mark it with the star by clicking on [blue]Great Post[/blue] link in his post.

Have fun.

---- Andy

There is a great need for a sarcasm font.