Unanswered: Populate Array with the query results and sort it
The question is already in the thread title. The code is bound to the On-Click event of a form, Access 2003 version. Now comes the confusing part:
The SQL string extracts fields CostCentre, CostCategory, SumOfCosts and Month from the table/another query. I'm struggling to populate the array with Month records only. It should look somewhat like this:
Then the code should loop through the array records and create a new textbox on the form once the month changes. For example, 31.10.2007 is unequal to 30.11.2007 --> create a new textbox.
If you don't understand why the heck anyone needs to do something this confusing and inefficient, I'll be happy to explain the whole background of the task to you - maybe, there's a more elegant solution. Arrays are just the only idea I've got.
This what I've got so far:
Set db = CurrentDb()
'SQL String to display results:
strSQL = "SELECT DISTINCT [Kostenart],[KoArt_Beschreibung],Sum([Kosten]) AS [Wert], [Bis]" & _
"FROM qryBasicSearch WHERE [KArtGr_Beschreibung] " & _
"IN(" & Criteria & ") AND [Kostenstelle] IN(" & Criteria1 & ")" & _
"AND [Bis]IN(#" & Periode & "#) " & _
"GROUP BY [Kostenart], [KoArt_Beschreibung], [Bis] " & _
"ORDER BY [Bis];"
Set rst = db.OpenRecordset(strSQL)
'count the records
i = .Fields.Count
j = .RecordCount
MyArray = rst.GetRows()
Dim x, y As Integer
'trying to display what's stored in the array
For x = 0 To UBound(MyArray) + 1
For y = 0 To j
MsgBox MyArray(y, x)
Set rst = Nothing
Any suggestions would be very much appreciated!!!!!!!!!!! Getting really desperate here.
Hello, georgev! Unfortunately that doesn't apply here. See, I have two multiselect listboxes on the form + 12 checkboxes that are Months... And I need to populate the array with the results that apply to all criterias only.
Thanks! But will it work for just one field of a 4-dimensional array?
What I'd actually need would be creating a query that splits the SumOfCosts results into different columns grouped by month. E.g. 3 columns for november, december and january if those months were selected on the form. I didn't think this was possible, that's why I went into the whole array situation...
SELECT DISTINCT [Kostenart],[KoArt_Beschreibung],Sum([Kosten]) AS [Wert], [Bis]FROM qryBasicSearch
WHERE [KArtGr_Beschreibung] IN("Materialkosten","Unterhaltskosten","EDV-Kosten")
AND [Kostenstelle] IN("3108","3115","3125")
GROUP BY [Kostenart], [KoArt_Beschreibung], [Bis]
ORDER BY [Bis];
But this yields [Wert] results in one column grouped by 31.10.2007, 30.11.2007 and 31.12.2007.
What I need is to split the [Wert] results in three different columns, the first of which calculates only october, the second only november, and the third only december. is it possible using a single SQL query, oder do I have to sort the array in VBA?
SELECT Sum(Kosten) AS [Wert]
WHERE KArtGr_Beschreibung IN ('Materialkosten', 'Unterhaltskosten', 'EDV-Kosten')
AND Kostenstelle] IN ('3108', '3115', '3125')
AND Bis IN (#10/31/2007#,#11/30/2007#,#12/31/2007#)