Eine Aggregationsfumktion um Feldinhalte auf einer Spalte zu gruppieren. Entspricht etwa dem LISTAGG() aus Oracle oder GROUP_CONCAT() as MySQL.
Download udf_dconcat.bas (V-2.1.0)
Ich brauchte die Funktion GROUP_CONCAT(), wie man sie in MySQL kennt. Kurze Suche im Web hat mir gezeigt, dass man so was am besten über DAO macht. Unter Flatting a Table (Using VBA) wurde ich fündig. Doch irgendwie war die Funktion nicht nach meinem Geschmack. Darum habe ich kurzerhand selber eine Funktion geschrieben.
Die Funktion sollte nicht innerhalb eines SQL auf grössere Datenmengen angewendet werden.
Die Performance wird stark darunter leiden
Hier werden nur die Funktionsheader gezeigt. Den vollständigen Code ist am Schluss der Seite.
Text = DConcat(Feld, Quelle [,Kriterium [,Delimiter [,Sortierung [,Eindeutigkeit]]]])
Public Function DConcat( _ ByVal iExpr As String, _ ByVal iDomain As String, _ Optional ByVal iCriteria As Variant = Null, _ Optional ByVal iDelimiter As String = ", ", _ Optional ByVal iOrderBy As Variant = Null, _ Optional ByVal iDistinct As Boolean = True _ ) As String
Der Rückgabelwert ist entweder eine Stringliste oder ein leerer String falls keine Details gefunden wurden. Sollte ein Fehler auftreten, wird #ERR und der Fehlertext zurückgegeben.
GroupConcat ist ein Alias zu DConcat. Die Funktion ist als veraltet markiert und ist nur noch Vorhanden, falls jemand die Funktion mit diesem Namen in seinem Proejkt führt. Einziger Unterschied nebst dem Funktionsnamen: iDistinct ist Default auf False.
Text = groupConcat(Feld, Quelle [,Kriterium [,Delimiter [,Sortierung [,Eindeutigkeit]]]])
Public Function groupConcat( _ ByVal iExpr As String, _ ByVal iDomain As String, _ Optional ByVal iCriteria As Variant = Null, _ Optional ByVal iDelimiter As String = ", ", _ Optional ByVal iOrderBy As Varian = Null, _ Optional ByVal iDistinct As Boolean = False _ ) As String
Eine einfache Abfrage auf eine Tabelle
'Enfache Abfrage. Duplikate werden nicht unterdrückt debug.print DConcat("set_name","my_table", "group_id=12") Montags-Report, TEST, TEST 'oder mit DConcat(). Duplikate werden standartmässig unterdrückt debug.print DConcat("set_name","my_table", "group_id=12") Montags-Report, TEST 'Die Unterdrückung kann auch aufgehoben werden. Zudem der Delimiter und die Sortierung angepasst debug.print DConcat("set_name","my_table", "group_id=12", "-", "mein_datum", false) TEST-Montags-Report-TEST
Die Funktion ist prädestiniert um innerhalb eines SQL-Statements anzuwenden. Damit wird die Funktion auf jede Zeile angewendet.
SELECT t.group_id, DCONCAT("set_name","my_table","group_id=" & t.group_id, , "mein_datum") FROM my_table AS t GROUP BY t.group_id
Man kann die Funktion auch in einem berechneten Textfeld eines Report-Fusses (oder Kopf) verwenden. Dabei kann man die Quelle und den Filter des Reports automatisch übernhemen
=DConcat("name";[RecordSource];IIf([FilterOn];[Filter];""))
Ebenfalls in einem Gruppenfuss. Der Filter muss um das Gruppierungsfeld erweitertwerden. In diesem Beispiel das Feld my_group_field.
=DConcat("name";[RecordSource];IIf([FilterOn];[Filter];"TRUE") & " AND [my_group_field] = " & [my_group_field])
Die meisten Felder sind normale SQL-String. Sprich es gelten die folgenden Regelen:
[]
geschrieben werden#MM/DD/YYYY#
, #HH:NN:SS#
bzw. #MM/DD/YYYY HH:NN:SS#
Siehe dazu auch [VBA][SQL] Filterstrings.
'Felder mit Leerzeichen und Sonderzeichen DConcat("[Mein Feld]","[Meine Tabelle]","[Filter Code]='Zürich'", ".", "[Mein Soriterfeld Nr.3]") 'Filterwert als Datum DConcat("MeinFeld","MeineTabelle","[Von Datum]>=#11/01/2015#") DConcat("MeinFeld","MeineTabelle","[Von Datum]>=" & Format([meinDatumsFeld], "\#mm\/dd\/yyyy\#")) 'Filterwert als String mit ' DConcat("MeinFeld","MeineTabelle","FilterCode>='abc'") DConcat("MeinFeld","MeineTabelle","FilterCode>='" & [filerFeld] & "'") 'oder mit "" DConcat("MeinFeld","MeineTabelle","FilterCode>=""abc""") DConcat("MeinFeld","MeineTabelle","FilterCode>=""" & [filerFeld] & """") 'Filter ist ein Boolean DConcat("MeinFeld","MeineTabelle","FilterCode=True") DConcat("MeinFeld","MeineTabelle","FilterCode=" & [meinBooleanFeld]) 'Filter ist eine Zahl DConcat("MeinFeld","MeineTabelle","FilterCode>=123") DConcat("MeinFeld","MeineTabelle","FilterCode>=" & [meinZahlenFeld])
Der Code ist zum Importieren gedacht. Wenn du ihn einfach in ein neues Modul kopierst, musst du den Header (Attribute-Zeilen) löschen.
Attribute VB_Name = "udf_dconcat" '------------------------------------------------------------------------------- 'File : udf_groupConcat.bas ' Copyright mpl by ERB software ' All rights reserved ' http://wiki.yaslaw.info/dokuwiki/doku.php/vba/access/functions/group_concat 'Environment : VBA 2010 + 'Version : 2.1.0 'Name : groupConcat 'Author : Stefan Erb (ERS) 'History : 00.00.0000 - ERS - Creation ' .. ' 23.12.2016 - ERS - Fehler behoben. GetString() kannte nicht mit einem leeren Delimiter umgehen ' 31.01.2017 - ERS - Fehler behoben. adClipString war nicht immer bekannt. Ist jetzt eine Konstante ' 03.07.2017 - ERS - GROUP_COCNAT() als @deprecated deklariert, replace() durch RegEx.replace() ersetzt '------------------------------------------------------------------------------- Option Explicit '/** ' * Simuliert ein GROUP_CONCAT() ' * http://blogannath.blogspot.ch/2011/01/microsoft-access-tips-tricks-flattening_28.html ' * String = DConcat(expr, domain [,criteria [,Delimiter [,OrderBy [,Distinct]]]]) ' * @param String Feld das zusammnegeführt werden soll ' * @param String Quelle (Tabelle, View) ' * @param String Where-Bedinung ohne WHERE ' * @param String Delimiter ' * @param String Order By Bedinung. Wenn nichts angegebn ist, wird nach iExpr aufsteigend sortiert ' * @param Boolean Werte als DISTINCT behandeln ' * @return String ' */ Public Function DConcat( _ ByVal iExpr As String, _ ByVal iDomain As String, _ Optional ByVal iCriteria As Variant = Null, _ Optional ByVal iDelimiter As String = ", ", _ Optional ByVal iOrderBy As Variant = Null, _ Optional ByVal iDistinct As Boolean = True _ ) As String Const C_EMPTY_DELIMITER = "\u0020" 'Ersatz Delimiter für GetString Const C_EMPTY_DELIMITER_PATTERN = "\\u0020" On Error GoTo Err_Handler 'Create SQL String Dim sql As String: sql = "SELECT " & IIf(iDistinct, "DISTINCT ", "") & iExpr & " AS item FROM " & iDomain If Not IsNull(iCriteria) Then sql = sql & " WHERE " & CStr(iCriteria) sql = sql & " ORDER BY " & NZ(iOrderBy, iExpr & " ASC") 'Handle Empty Delimiter. GetString() kann ncith mit einem Leeren Delimiter umgehen. Dim delimiter As String: delimiter = IIf(iDelimiter = Empty, C_EMPTY_DELIMITER, iDelimiter) 'Get the Delimited String DConcat = CurrentProject.connection.execute(sql).GetString(, , , delimiter) 'Remove the Delimiter at the End of the String: '1000, 1144, 3000, ' -> '1000, 1144, 3000' If DConcat Like "*" & delimiter Then DConcat = Left(DConcat, Len(DConcat) - Len(delimiter)) 'Wennd er Delimiter leer ist, den Ersatz entfernen If iDelimiter = Empty Then Static rx As Object: If rx Is Nothing Then Set rx = CreateObject("VBScript.RegExp"): rx.pattern = C_EMPTY_DELIMITER_PATTERN: rx.Global = True DConcat = rx.Replace(DConcat, "") 'DConcat = Replace(DConcat, C_EMPTY_DELIMITER, iDelimiter) End If Exit_Handler: On Error Resume Next Exit Function Err_Handler: DConcat = "#ERR: " & Err.DESCRIPTION & " (" & sql & ")" Resume Exit_Handler Resume End Function '/** ' * @deprecated ' * Alias zu DConcat(). Ist noch vorhanden, da ich alte Projekte mit diesem Namen habe ' * Im Gegensatz zu DConcat() ist iDistinct standardmässig auf False ' */ Public Function groupConcat( _ ByVal iExpr As String, _ ByVal iDomain As String, _ Optional ByVal iCriteria As Variant = Null, _ Optional ByVal iDelimiter As String = ", ", _ Optional ByVal iOrderBy As Variant = Null, _ Optional ByVal iDistinct As Boolean = False _ ) As String groupConcat = DConcat(iExpr, iDomain, iCriteria, iDelimiter, iOrderBy, iDistinct) End Function