INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Listbox Display Aggregate and feed query it's hiearchal pieces

Listbox Display Aggregate and feed query it's hiearchal pieces

(OP)
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

RE: Listbox Display Aggregate and feed query it's hiearchal pieces

"recognize 2016C, 2017F and 2017I " - I assume you want to pick just the BLUE part (left 5 characters) of your selected items in the list box (right?)

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))

If so, try:

CODE

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.

RE: Listbox Display Aggregate and feed query it's hiearchal pieces

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

RE: Listbox Display Aggregate and feed query it's hiearchal pieces

(OP)
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?

RE: Listbox Display Aggregate and feed query it's hiearchal pieces

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Listbox Display Aggregate and feed query it's hiearchal pieces

(OP)
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.

RE: Listbox Display Aggregate and feed query it's hiearchal pieces

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

RE: Listbox Display Aggregate and feed query it's hiearchal pieces

(OP)
Duane, worked great - your suggestion..thanks!

RE: Listbox Display Aggregate and feed query it's hiearchal pieces

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close