VBA-Funktionen in Abfragen / SQL

Nicht immer kann man alles mit den Access-Standardfunktionen erledigen, was einem täglich so auf den Schreibtisch kommt. Nehmen wir dazu ein einfaches Beispiel: Die Firma 'Plastik-Meyer' stellt Eimer, Untersetzer, Schalen und Gießkannen für eine Baumarktkette her.
Die Artikelnummern von Plastik-Meyer sind in der Form xxxxfxx aufgebaut, wobei f ein Farbcode ist. Die Tabelle zeigt, welche Farbe sich hinter f verbirgt.

Frage: Wie kann eine Abfrage die Artikel nach ihrer Farbe sortieren ?

Damit eine Funktion überhaupt für eine Abfrage benutzt werden kann müssen alle Variablen, die übergeben und zurückgegeben werden, vom Typ Variant sein. Der Grund liegt darin, daß in einer Access-Spalte vom Typ 'Long Integer' Daten sowohl vom Typ 'Long Integer' stehen können, aber auch der Wert NULL. Dies gilt nicht nur für den Typ 'Long Integer', sondern für alle Typen: Der Wert NULL kann in Feldern jedes Typs gespeichert werden.

Anders in VBA: Hier gehört der Wert NULL zu keinem Datentyp außer 'Variant'. Übergibt men einer Funktion die NULL als Argument, dann wird sie in einen anderen Typ umgewandelt, sofern das Argument nicht vom Typ Variant ist.
In Abfragen erfordert NULL jedoch häufig eine Sonderbehandlung, wenn besipielsweise keine Artikelnummer angegeben wurde (Artikelnummer ist NULL), dann kann auch keine Farbe bestimmt werden, der Rückgabewert für die Farbe ist sinnvollerweise ebenfalls NULL.

Eine Funktion sollte dabei so aussehen:

Public Function Farbe(ArtikelNr As Variant) As Variant
    If IsNull(ArtikelNr) Then Exit Function
        ...
        ' Anweisungen
        ...
End Function

Die Typangabe 'As Variant' kann in VBA immer weggelassen werden.

Das Beispiel der Farbe läßt sich mit einer VBA-Funktion lösen, die sich in einem Standardmodul befindet.

Public Function ArtikelFarbe(ArtikelNr As Variant) As Variant
    If IsNull(ArtikelNr) Then Exit Function
        Select Case (ArtikelNr Mod 100) \ 10
            Case 0: ArtikelFarbe = "Sonderfarbe"
            Case 1: ArtikelFarbe = "rot"
            Case 2: ArtikelFarbe = "orange"
            Case 3: ArtikelFarbe = "gelb"
            Case 4: ArtikelFarbe = "grün"
            Case 5: ArtikelFarbe = "blau"
            Case 6: ArtikelFarbe = "braun"
            Case 7: ArtikelFarbe = "schwarz"
            Case 8: ArtikelFarbe = "grau"
            Case 9: ArtikelFarbe = "weiß"
        End Select
End Function

Das Beispiel geht davon aus, daß der Datentyp der Artikelnummer in der zugrundeliegenden Tabelle auf 'Byte', 'Integer' oder 'Long Integer' eingestellt wurde. Wenn der Datentyp in der Tabelle vom Typ Text / string ist, dann muß in Zeile 3 auch folgender Code stehen:
Select Case Left(Right(CStr(ArtikelNr), 2), 1)

In die Abfrage wird nun ein einfacher Aufruf der Funktion eingesetzt.
Das Wort 'Farbe' bestimmt dabei den Namen der Ausgabespalte.
In der Zeile 'Kriterien' kann nun auch eine Bedingung eingesetzt werden, z.B. sorgt <>"schwarz" dafür, daß alle Farben außer schwarz angezeigt werden.
Zum Sortieren kann man nun in der Zeile 'Sortierung' die Auswahl zwischen absteigend oder aufsteigend wählen.

Wenn man die Farben nach Helligkeit sortieren will, dann muß man noch einmal in die Programmierung und vor die Farbnamen in der VBA-Funktion noch einen Text einfügen, der die Sortierung erzwingt, z.B. "1 weiß", "2 gelb", ... oder "A weiß", "B gelb", ...

• In Abfragen können nur Funktionen (Function) verwendet werden, keine Prozeduren (Sub). Dies gilt auch, wenn die Funktion wirklich keine Werte zurückgegeben soll.

• Die Funktionen müssen in einem Standardmodul als 'Public' definiert sein.

• Die Argumente müssen vom Typ 'Variant' sein, sonst kann die Funktion den Wert NULL in den übergebenen Argumenten nicht erkennen und behandeln.

• Wenn der Rückgabewert NULL sein kann, muß er ebenfalls als Variant definiert sein. Um eine NULL zurückzugeben, genügt es, die Funktion zu verlassen ohne dem Funktionsnamen einen Wert zuzuweisen.

• Vorsicht: Keine Namen verwenden, die von Access schon belegt werden. In diesem Besipiel wäre der Name 'Farbe' für die Funktion tödlich, da Access den Begriff 'Farbe' kennt und in der SQL-Ansicht in QBColor übersetzt -> Nix geht !< /P>

• Tests mit neuen VBA-Funktionen sollten immer nur mit wenigen Datensätzen stattfinden - es ist nämlich sehr ärgerlich, wenn ein Fehler in VBA auftritt, weil man die Abfrage nicht anhalten kann. Jeder Fehler muß einzeln mit 'Beenden' oder 'Abbrechen' quittieren werden. Man kann so aber die Abfrage nicht daran hindern, die fehlerhafte VBA-Funktion für den nächsten Datensatz erneut aufzurufen.

Das Beispiel war sehr einfach, um das Prinzip zu zeigen. Es ist aber auch möglich, die Farbunterscheidung ohne VBA mit einer völlig unübersichtlichen SQL-Anweisung zu lösen. Aufgabe: Machen ; Tipp: Funktion 'Choose' beachten.