Natürliche Zahlensortierung in Access mit VBA

von Philipp Stiefel, ursprünglich veröffentlicht am 19.09.2016, zuletzt aktualisiert 31.10.2019

Foto von Alex Wong, hier verwendet unter der CC0 Lizenz

Du magst fragen was „natürliche Zahlensortierung“ ist. – Lass es mich erklären.

Du als Entwickler solltest den Unterschied zwischen einem numerischen Datentyp und einem Textdatentyp kennen. Wenn du Zahlen als Text speicherst, werden sie anders sortiert, als in einem richtigen numerischen Datentyp. Hier eine kurze Erinnerung an den Unterschied in der Sortierung.

Wir als Entwickler verstehen die Auswirkungen von Datentypen und würden beide oben stehen Sortierungen unter Berücksichtigung der Datentypen als völlig logisch ansehen. – Wenn wir allerdings unseren Benutzern eine Liste von Zahlen vorsetzen, die so sortiert ist, wie in der Text-Datentyp-Spalte oben, würden sie denken, wir haben den Verstand verloren.

Zahlensortierung

Für das einfache Beispiel oben ist die Lösung genauso einfach. Wir verwenden einen numerischen Datentyp und alle sind zufrieden.

Aber was ist mit dieser Spalte, die Beschreibungen von Verpackungseinheiten enthält? (Beide Spalten enthalten exakt die gleichen Werte, sie sind nur anders sortiert.)

Die beiden Spalten enthalten genau die gleichen Daten, sie sind nur jeweils anders sortiert. Welche Variante würdest du bevorzugen?

Es würde mich sehr überraschen, wenn irgendwer tatsächlich vorziehen würde, diese Tabelle nach Sortiervariante A sortiert zu sehen. Aber genau das ist, was passiert wenn du die Daten in einer Text-Spalte abspeicherst und dann nur nach dieser Spalte sortierst. – Hässlich. Du könntest Abhilfe schaffen indem du nach einer weiteren, versteckten Spalte sortierst, die eine Reihenfolge wie in Variante B vorgibt. – Aber wie wird diese Hilfsspalte verwaltet?

Wäre es nicht toll, wenn du den Text, so wie er ist, ohne zusätzliche Hilfsspalte, nach der Variante A sortieren könntest indem du nur einen speziellen Sortieralgorithmus verwendest?

Kürzlich hat jemand eine solche Frage zur Sortierung im Access-O-Mania-Forum gestellt. Ich fand diese Frage sehr interessant, daher habe ich dazu ein paar Recherchen angestellt.

Die Windows API kann alles

Microsoft hat mit Windows XP die logische Zahlensortierung im Windows Explorer eingeführt. Also dachte ich, dass es eine Windows-API-Funktion für diesen Zweck geben könnte.

Das erste Fundstück war die StrCmpLogicalW Funktion, diese implementiert genau diesen numerischen Sortieralgorithmus, der Zahlen nach ihrem numerischen Wert sortiert. Leider kann diese Funktionier nur dazu verwendet werden, um zwei String-Zeichenfolgen miteinander zu vergleichen.

Wesentlich geeigneter für unsere Zwecke ist die LCMapStringEx Funktion, die einen SortKey (Sortierschlüssel) erzeugt, der verwendet werden kann, um mehrere Werte, wie die in einer Datenbanktabelle, auf Basis dieses Algorithmus zu sortieren.

LCMapStringEx ist nicht die einfachste API-Deklaration, aber ich habe sie hinbekommen. Hier ist meine Implementierung von GetSortKey. Einer VBA-Funktion, die diese API kapselt und den SortKey mit dem SORT_DIGITSASNUMBERS-Flag ermittelt. Der resultierende SortKey ist ein Bytearray, das unter Berücksichtigung der natürlichen Zahlensortierung erstellt wurde.

Achtung: Der SORT_DIGITSASNUMBERS-Flag ist von Windows 7  an verfügbar.

Ich werde hier jetzt weder die Details der API-Funktion noch die meines Wrappers darum auseinandernehmen. Ich schlage vor, dass du bei Bedarf in der Dokumentation der LCMapStringEx -Funktion nachliest.

Nur eine Sache, die aus der VBA-Perspektive sicherlich seltsam aussieht. Wenn du die die Konstanten LOCALE_NAME_USER_DEFAULT und LOCALE_NAME_INVARIANT anschaust, könntest du denken, die Werte von vbNullString und “” sind ja effektiv die gleichen. – Das wären sie im VBA-Kontext, aber nicht im Kontext der API. vbNullString is ein 0-Zeiger während “” einen echten Zeigerwert auf einen leeren String beinhaltet.

So weit so gut. Die Funktion funktioniert, aber vorerst musst du mir das einfach glauben, denn wir haben noch ein anderes Problem vor uns. Der SortKey, der von LCMapStringEx erstellt wird, ist ein Bytearray. Was machen wir damit?

1. Ansatz – Den binären SortKey als String behandeln

In Access können nur Funktionen, die einen einfachen Datentyp, wie einen String oder numerische Typen zurückgeben, innerhalb von Abfragen verwendet werden. Funktionen mit komplexen Rückgabewerten, wie z.B. Objekten, Typen/Strukturen und Arrays sind nicht zulässig.

Der gängigste Lösungsansatz für dieses Problem wäre es, eine VBA-Funktion zu schreiben, die uns einen String aus den Bytes des SortKeys erstellt. Diese Funktion in einer Abfrage zu verwenden indem wir ihr die Basiswerte als Input übergeben und dann die Abfrage über den Rückgabewert der Funktion sortieren.

Dieser Ansatz ist nicht absolut ideal in Hinblick auf die Performance, aber er würde funktionieren. – Normalerweise. – Hier jedoch nicht!

Es sieht auf den ersten Blick eigentlich ziemlich geradlinig aus. Es ist möglich ein Bytearray direkt in einen Unicode String umzuwandeln. Der String kann von der VBA-Funktion zurückgegeben werden und man kann dann in der Abfrage danach sortieren. – Einfach. – Außer, dass es nicht funktioniert.

Das Bytearray das uns von LCMapStringEx als SortKey zurückgeliefert wird, ist ein echtes binäres Bytearray. Wenn es von einer Funktion in einen String umgewandelt wird, behandelt Access es auch entsprechend. Das bedeutet, dass Access auf diesen String die Datenbank Sortierreihenfolge anwenden wird, wenn wir in einer Abfrage danach sortieren.

Hier ein kleines Beispiel des Problems: Als Text behandelt, sind die Buchstaben a A b B c C hier richtig sortiert. (Details hängen von der verwendeten Sortierreihenfolge ab.) Aber wenn man sie als Binärdaten betrachtet, wäre die korrekt Sortierreihenfolge: A B C a b c. – Gänzlich verschieden. Um den SortKey, der von LCMapStringEx erstellt wurde, zu verwenden, müssen wir ihn unbedingt wie binäre Daten behandeln.

Dieser Ansatz funktioniert also nicht zum Zweck der Sortierung. Er löst also nicht unser Hauptproblem. Er könnte aber verwendet werden, um gespeicherte SortKey-Daten in einer Aktualisierungsabfrage zu aktualisieren. Die Daten in dem Unicode String sind tatsächlich korrekt, sie werden nur nicht so sortiert, wie wir es benötigen.

2. Ansatz – Den binären SortKey in Access speichern

OK, wir haben binäre Daten und müssen sie auch als solche speichern und sortieren. Einmal mehr stellt sich heraus, dass etwas, was eigentlich trivial sein sollte, die in Access leider nicht ist. The wohlbekannte Datentyp für Binärdaten in Access ist das OLE-Objekt. Aber dieser Typ ist für große Binärdateien vorgesehen und daher nicht konzeptbedingt nicht sortierbar.

Glücklicherweise erinnerte mich jemand an den Binär-Datentyp in Access. Dieser ist perfekt geeignet für unsere Aufgabe. Allerdings brauchst du ein wenig Hintergrundwissen, um diesen Datentyp in Access zu verwenden. Ich habe alle Fakten in einem Artikel über den Binär-Datentyp zusammengefasst.

Wir können also unser Problem lösen, indem wir eine zusätzliche Binär-Spalte in der Tabelle, in der wir die logische Zahlensortierung verwenden wollen, erstellen. Dann schreiben wir eine kleine VBA-Prozedur um alle Datensätze in der Tabelle mit ihrem SortKey zu aktualisieren. Zuletzt sortieren wir dann die Tabelle nach der SortKey-Spalte und, voila, wir haben die natürliche Zahlensortierung implementiert.

Hier ist ein Screenshot der Tabelle, sortiert nach der zusätzlichen SortKey-Spalte.

Achtung: Wenn du den SortKey in der Datenbank abspeicherst, musst du sorgfältig überlegen, welches Gebietsschema (LOCALE), du für LCMapStringEx verwendet werden soll. I würde nicht empfehlen, das Standard-Gebietsschema des Benutzers zu verwenden, denn das für jedem Benutzer anders sein.

Dieser Ansatz funktioniert, aber er hat eine offensichtliche Schwäche: Aktualisierungsanomalien.

Wir haben jetzt ein zusätzliches Feld für unseren SortKey erstellt. Der Inhalt dieses Feldes ist direkt von dem primären Textfeld, das die eigentlichen Daten enthält, abhängig. Wenn der Text aktualisiert wird, muss auch der SortKey aktualisiert werden.

Also musst du jetzt entweder in jedem Formular, in dem der Text geändert werden kann, per Code den SortKey neu berechnen, oder du muss immer bevor die Tabelle mit der natürliche Zahlensortierung  sortiert werden soll, die SortKeys der kompletten Tabellen auf den aktuellen Stand aktualisieren.

Die erste Variante versagt, wenn die Texte mit einer Aktualisierungsabfrage geändert werden. Die zweite Variante benötigt einige Rechenzeit, von der die meiste eigentlich unnötig ist, wenn sich nur wenige Daten geändert haben. – Beides keine idealen Lösungen.

Vielleicht könnte es in einem Access-Backend ein Datenmakro übernehmen, die SortKeys immer auf dem aktuellen Stand zu halten. – Zur Zeit weiß ich nicht genug über Datenmakros, um das genauer auszuführen. – Ich werde das vielleicht zu einem späteren Zeitpunkt nachholen.

Zusätzlich zu den Aktualisierungsanomalien hat dieser Ansatz einen anderen Nachteil. Du musst die Datenstruktur jeder Tabelle ändern, in der die natürliche Zahlensortierung verwendet werden soll. Im Positiven ist anzumerken, dass die Performance bei den Sortiervorgängen sehr gut ist und bei Bedarf noch weiter verbessert werden kann, wenn du einen Index auf der Binär-Spalte der Tabelle anlegst.

3. Ansatz – Hex-String-Funktion

Wie wir wissen, kann Access nur Funktionen die Strings (oder Zahlen) zurückgeben in Abfragen verwenden. Einfach das SortKey-Bytearray direkt in einen String umzuwandeln funktioniert nicht, wegen der Probleme mit der Sortierreihenfolge. Aber was wäre, wenn wir die Binärdaten des SortKey in eine String-Repräsentation umwandeln, die korrekt sortiert wird? – Klingt vielversprechend.

Die kompakteste Form um Binärdaten in einem String darzustellen, ist die Hexadezimalschreibweise. Also habe ich eine Funktion geschrieben, die das binäre Bytearray in einen Hexadezimalstring konvertiert.

Damit das Sortieren korrekt funktioniert, müssen wir eine führende Null voranstellen, wenn der Hex-Wert nur einstellig ist. Damit erreichen wir eine gleichförmige Länge der Ausgabewerte pro Byte.

Jetzt kannst du diese Funktion in jeder beliebigen Abfrage verwenden, um eine virtuelle Sortierungsspalte zu erzeugen. Nach dieser Spalte zu sortieren, liefert die korrekten Ergebnisse, da die text-basierende Sortierreihenfolge der Hex-Werte mit der numerischen Sortierreihenfolge der Binärdaten korrespondiert. – Dieser Ansatz funktioniert sehr gut. J

Hier ist der SQL-Text einer solchen Abfrage:

Und hier ist das Ergebnis der Abfrage:

Sei dir aber bewusst, dass diese Funktion in Hinsicht auf die Performance nicht ideal ist. Die String-Verarbeitung ist relativ langsam und es müssen immer alle Datensätze verarbeitet werden um die Sortierung zu ermitteln. Diese Funktion sollte man also nur für eine relativ kleine Menge an Datensätzen verwenden.

Fazit und Danksagung

Die natürliche Zahlensortierung ist ein mächtiges Feature um die Benutzerfreundlichkeit deiner Anwendung zu verbessern. Du solltest auf jeden Fall über diese Möglichkeit, sie in dein Access Projekt zu integrieren, bescheid wissen.

Keiner der hier gezeigten Lösungsansätze ist die non-plus-ultra Lösung für alle Situationen, in denen du vielleicht die logische Sortierung anwenden willst. Du solltest deine Anforderungen gründlich analysieren bevor du dich für einen oder mehrere der hier skizzierten Lösungswege entscheidest.

Zum Abschluss möchte ich daolix und Lachtaube für ihre unbezahlbaren Beiträge zu diesem Thema auf Access-O-Mania danken. Alle Informationen in diesem Artikel sind mehr das Ergebnis unserer gemeinsamen Anstrengungen, als nur meiner alleine.

Lieber Leser, vielen Dank für deine Aufmerksamkeit.

Hier kannst du ein VBA-Modul mit kompletten VBA-Code aus diesem Artikel herunterladen: modNumeralsSorting.

Share this article: