Wozu VBA-Funktionen in Abfragen ? |
0 | Sonderfarbe oder Farbe passt nicht ins
Schema |
1 | rot | 2 | orange | 3 | gelb |
4 | grün | 5 | blau | 6 | braun |
7 | grau | 8 | schwarz | 9 | weiß |
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 ? |
Allgemeiner Aufbau einer VBA-Funktion |
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.
|
Aufbau der Funktion |
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) |
Einbau in die Abfrage |
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", ...
|
Allgemeine Hinweise |
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.
|
Kleine Denksportaufgabe |
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. |