Unanswered: Multiselect listbox & 12 Checkboxes to filter a form
I need made a search form to filter my query (which is based on 5 tables). The search form contains:
- Multiselect listbox Cost Centre
- Multiselect listbox Cost Category
- 12 Checkboxes each representing a month
The user should be able to choose multiple cost centres, multiple cost categories and sum the costs for these criterias. However, costs for each single selected month should be listed separately. So, for example if the user chooses cost centres 1 and 2, cost categories a, b and c, and months october, november and december, the search output should look like this:
Cost Category group October November December
Salary 12'878 1'277 43'678
I haven't figured out yet how to display all the requested months separately on one form...
This is what I have so far, but there's an error in the code:
Dim Q As QueryDef, db As Database
Dim ctl As Control
Dim ctl1 As Control
Dim Itm As Variant
Dim strWhere As String
Dim Criteria As String
Dim Criteria1 As String
Dim Monat As Date
Set ctl = Me!lbKostenartengruppe
Set ctl1 = Me!lbKostenstelle
'filter by cost category group:
For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else: Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) & Chr(34)
'filter by cost centre:
For Each Itm In ctl1.ItemsSelected
If Len(Criteria1) = 0 Then
Criteria1 = Chr(34) & ctl1.ItemData(Itm) & Chr(34)
Else: Criteria1 = Criteria1 & "," & Chr(34) & ctl1.ItemData(Itm) & Chr(34)
'if nothing is chose, show a MsgBox:
If Len(Criteria) = 0 Or Len(Criteria1) = 0 Then
Itm = MsgBox("Keine Auswahl vorhanden.")
'if checkbox October is clicked, set the date category as 31.10.2007:
If Me.ckOktober.Value Then
Monat = #10/31/2007#
'when the code finally works, add eleven more If-Then-Else loops for each month
Set db = CurrentDb()
Set Q = db.QueryDefs("qrySearch_MonatGetrennt")
'SQL String to display results:
'qryBasicSearch pulls all the needed fields from 5 tables, qrySearch_MonatGetrennt is based on qryBasicSearch and is used to calculate the costs
Q.SQL = "SELECT DISTINCT [Kostenart],[KoArt_Beschreibung],Sum([Kosten]) AS [Wert]" & _
"FROM qryBasicSearch WHERE [KArtGr_Beschreibung] " & _
"IN(" & Criteria & ") AND [Kostenstelle] IN(" & Criteria1 & ")" & _
"AND [Bis] = #" & Monat & "#" & _
"GROUP BY [Kostenart], [KoArt_Beschreibung];"
'show the results on a separate form, which is based on qrySearch_MonatGetrennt:
I get run-time error "Syntax error in Date/Time in SQL String".
And when it works: HOW do I get to calculate and display each month separately on the output form - "FrmAusgabe_Monat"?????????
I added the space before GROUP BY, but it didn't change anything...
Okay, here's a piece from the immediate window - I debug.prined the SQL string.
SELECT DISTINCT [Kostenart],[KoArt_Beschreibung],Sum([Kosten]) AS [Wert]FROM qryBasicSearch
WHERE [KArtGr_Beschreibung] IN("Unterhaltskosten","Mietkosten")
AND [Kostenstelle] IN("3100","3108","3125")AND [Bis] = #31.10.2007#
GROUP BY [Kostenart], [KoArt_Beschreibung];
As for problem 2, I think you might have to do a fair bit of coding to get that to work. The code would have to examine what is selected on the form and then set the Control Source of text boxes and the Caption of labels accordingly.