Das Access-Magazin für alle, die schnell von 0 auf 100 wollen
Adresse info@access-basics.de. Ich freue mich na- türlich auch über Lob und Kritik – also nur los!
Nun aber viel Spaß beim Lesen!
André Minhorst
In dieser Ausgabe:
Tabellen entwerfen – Teil V: Eigenschaften von Tabellen | Lernen Sie die Eigenschaften von Tabellen wie Gültigkeits- regeln, Filter, Sortierungen und Co. kennen.
Der Ausdrucks-Generator – Ausdrücke schnell zusam- menstellen mit einem praktischen Tool | Nutzen Sie den Ausrucks-Generator, um schnell Ausdrücke für Feldwerte, Kriterien und Eigenschaften zusammenzustellen
Abfragen für die Datenauswahl – Teil VI: Berechnete Fel- der in Abfragen und Eigenschaften | Verwenden Sie Ab- fragen, um Texte mehrerer Felder zu verknüpfen oder Be- rechnungsergebnisse anzuzeigen
Abfragen für die Datenauswahl – Teil VII: Benutzerdefi- nierte Funktionen in Abfragen | Wählen Sie gebundene und ungebundene Daten mit dem Kombinationsfeld aus!
Access-Funktionen – Teil I: Domänenfunktionen | Fra- gen Sie schnell mit einem einzigen Befehl Daten aus Ta- bellen oder Abfragen ab
Impressum
Access [basics] wird monatlich herausgegeben von:
André Minhorst | Fachverlag für Softwareentwicklung | Borkhofer Straße 17 | 47137 Duisburg
Die hier veröffentlichten Texte sind urheberrechtlich geschützt. Übersetzung und Vervielfältigung bedürfen der aus- drücklichen schriftlichen Genehmigung des Verlages. Sämtliche Veröffentlichungen in Access [basics] erfolgen ohne Berücksichtigung eines eventuellen Patentschutzes, auch werden Warennamen ohne Gewährleistung einer freien Verwendung benutzt.
André Minhorst Fachverlag für Softwareentwicklung übernimmt für beschriebene oder zum Download bereitstehende Programme weder Gewähr noch Haftung, außer für Vorsatz oder grobe Fahrlässigkeit. Bezugspreise erfahren Sie auf www.access-basics.de.
Redaktion:
André Minhorst (V.i.S.d.P) | Telefon: 0203/4495577 | E-Mail: info@access-basics.de | Internet: www.access-basics.de Geschäftsführung, Herstellung, Text- und Schlussredaktion, Layout von Magazin und Webseite: André Minhorst Autoren: André Minhorst
ISSN: 2190-8761
Access [basics]-News
Geschafft! Pünktlich auf die Minute werden Sie um kurz vor 0:00 Uhr, also noch am 31. Januar 2011, die Mail mit dem Link zu dieser neuen Ausgabe von Access [basics] im Postfach vorfinden. Da die letzte Ausgabe des Jahres 2010 erst Anfang Januar 2011 erschien, hatte ich ja versprochen, dass die erste Ausgabe des neuen Jahres ebenfalls noch im Januar erscheint. Und das war gar nicht einfach: Immerhin steht im Moment noch das neue Access 2010 - Das Grundlagenbuch für Entwickler auf dem Programm, und auch der eine oder andere Kunde hat Anfang des Jahres Wünsche für seine Softwareprojekte.
In dieser Ausgabe gibt es noch einmal einen Rutsch Grundlagen zu Tabellen und Abfragen, außerdem stelle ich Ihnen mit den Domänenfunktionen eine sehr einfache Möglichkeit vor, auf die Daten inner- halb Ihrer Tabellen zuzugreifen.
Die nächste Ausgabe wird dann richtig spannend: Wir setzen die Arbeiten an der Projektzeiterfassung fort und werden die Formulare zum Eingeben von Aufga- ben und Tätigkeiten der einzelnen Projekte erstellen.
Mit den Grundlagen der letzten Ausgaben werden wir eine Menge schaffen.
Übrigens: Wenn Sie einmal Themenwünsche haben, dürfen Sie mir diese gern mitteilen, und zwar an die
Die Eigenschaften einer Tabelle finden Sie in der Entwurfsansicht.
Klicken Sie hier mit der rechten Maustaste auf eines der Felder und wählen Sie den Kontext- menüeintrag Eigenschaften aus, um das Eigenschaftsfenster wie in Bild 1 anzuzeigen (hier unter Access 2010). Die folgenden Ab- schnitte stellen die Eigenschaften und ihre Bedeutung vor.
Beschreibung (ab Access 2000)
Die Eigenschaft Beschreibung nimmt einen Text auf, der die Ta- belle beschreibt. Dieser Text wird beispielsweise wie in Bild 2 im Navigationsbereich angezeigt (Ac-
cess 2007 und jünger). Dazu müssen Sie allerdings den Eintrag Anzeigen nach|Details aus dem Kon- textmenü des Navigationsbereich-Kopfes auswäh- len. Access 2003 und älter zeigen diesen Text im Da- tenbankfenster an (s. Bild 3).
Standardansicht (2003)
Die Standardansicht kann die drei Werte Datenblatt, PivotTable und PivotChart annehmen. Die Daten- blattansicht kennen Sie bereits, die beiden übrigen Ansichten nicht. Das werden wir an dieser Stelle auch nicht ändern – es würde schlicht den Rahmen sprengen. Stattdessen widmen wir diesen beiden Ansichten, die übrigens auch in Formularen und Be- richten eine Rolle spielen, in einem eigenen Artikel in einer späteren Ausgabe von Access [basics].
Gültigkeitsregel und Gültigkeitsmeldung (2000)
Diese beiden Eigenschaften kennen Sie eigentlich schon, da der oben genannte Artikel zum Thema Feldeigenschaften sie bereits vorgestellt hat. Aller- dings bieten die Eigenschaften in Zusammenhang mit Tabellen andere Möglichkeiten. So können Sie diese beiden Eigenschaften nicht nur zum Prüfen Bild 2: Be-
schreibung einer Tabelle im Navigati- onsbereich ...
Tabellen entwerfen
Teil V: Eigenschaften von Tabellen
Im vorherigen Teil dieser Artikelreihe haben wir uns die Eigenschaften der Felder einer Tabelle angesehen.
Aber auch Tabellen selbst haben einige Optionen, die das Verhalten und das Aussehen steuern. Dieser Arti- kel beschreibt, welche Eigenschaften Tabellen haben und wie Sie diese einsetzen können.
Bild 1: Anzeigen des Eigenschaftsfensters einer Access-Tabelle
Bild 3: ... und im Datenbankfenster.
des Feldinhalts selbst verwenden.
Stattdessen erweitern Sie den zu prüfenden Ausdruck einfach auf meherere Felder. Bild 4 zeigt ein Beispiel: Die Tabelle tblProjekte enthält zwei Felder namens Pro- jektstart und Projektende. Das Enddatum soll logischerweise nicht vor dem Startdatum liegen.
Wenn Sie nicht vorbeugen, wird es dem Benutzer in der Regel den- noch gelingen, ungültige Werte einzugeben. Also tragen Sie für die Gültigkeitsregel der Tabelle den folgenden Ausdruck ein:
[Projektstart]<[Projektende]
Beachten Sie, dass die Gültigkeits- regel einen Ausdruck enthalten muss, der die gültigen Daten ent- hält! Die Eigenschaft Gültigkeits- meldung erhält diesen Wert:
Der Projektstart muss vor dem Pro- jektende liegen.
Wenn Sie nun eine ungültige Kom- bination von Werten eintragen,
zeigt Access die Gültigkeitsmeldung an – allerdings erst, wenn Sie versuchen, den Datensatz zu spei- chern (siehe Bild 5). Im Gegensatz zu den beiden gleichnamigen Eigenschaften von Tabellenfeldern gibt es also zwei wesentliche Unterschiede:
• Auf Tabellenebene können Sie alle Felder der Tabelle in der Gültigkeitsregel referenzieren. Auf Feldebene gelingt dies nur für das jeweilige Feld.
• Auf Tabellenebene erfolgt die Gültigkeitsprüfung vor dem Speichern des Datensatzes, auf Feld- ebene vor dem Aktualisieren des Feldinhalts.
Man kann hier auch kompliziertere Ausdrücke zu- sammensetzen. Dabei ist einem der Ausdrucks-Ge- nerator behilflich. Diesen beschreiben wir in einem weiteren Artikel namens Der Ausdrucks-Generator.
Filter und Sortiert nach (2000)
Mit der Filter-Eigenschaft stellen Sie einen Filter ein, zum Beispiel KundeID = 1111. Diesen aktivie- ren Sie in der Datenblattansicht unter Access 2003 und älter, indem Sie den Kontextmenüeintrag Filter/
Sortierung anwenden der Titelzeile des Datenblatts auswählen (Filter/Sortierung entfernen zeigt wie- der alle Daten an).
In neueren Access-Versionen, also Access 2007 und jünger, steht dieser Kontextmenübefehl nicht mehr zur Verfügung. Sie können den Filter stattdessen über einen entsprechenden Ribbon-Eintrag aktivie- ren.
Bild 4: Beispiel für den Einsatz von Gültigkeitsregel und Gültigkeitsmeldung
Bild 5: Anzeige der Gültigkeitsmeldung beim Verletzten einer Gültigkeitsregel
Bild 6: Tabelle mit Unterdatenblättern
Die Filter-Eigenschaft ist im Tabellenentwurf aller- dings nicht besonders sinnvoll – Sie können schließ- lich nur einen Filterausdruck damit definieren, und das auch noch manuell.
Gleiches gilt für die Eigenschaft Sortiert nach: Die Sortierung werden Sie genau wie das Filtern eher in einer Abfrage vornehmen, die einem Formular oder einem Bericht zugrunde liegt. Die Sortiert nach-Ei- genschaft macht höchstens Sinn, wenn Sie die Tabel- le in der Datenblattansicht nach einem anderen Feld als dem Primärschlüsselfeld sortieren wollen. Auch wenn Sie eine Tabelle mit einem Sortiert nach-Aus- druck als Datenherkunft für ein Formulard verwen- den, wird die in Sortiert nach angegebene Sortierung beibehalten.
Als Wert für diese Eigenschaft verwenden Sie eine durch Kommata getrennte Liste der Feldnamen in der Reihenfolge, in der sortiert werden soll. Für auf- steigende Sortierung fügen Sie das Schlüsselwort ASC hinzu, für absteigende Sortierung DESC. Wenn Sie aufsteigend nach den Nachnamen und abstei- gend nach der Kundennummer sortieren möchten, lautet das Kriterium also beispielsweise Nachname ASC, KundeID DESC.
In Abfragen auf Basis der betroffenen Tabellen wirkt die Sortierung allerdings nicht mehr. Auch Listen- felder, Kombinionsfelder und Berichte auf Basis der Tabelle stören sich nicht an der angegebenen Sor- tiervorschrift.
Beim Laden filtern (2007)
Mit dieser Eigenschaft legen Sie fest, ob der mit der Filter-Eigenschaft angegebene Filter gleich beim Öffnen der Tabelle in der Datenblattansicht ange- wendet wird.
Beim Laden sortieren (2007)
Gleiches gilt für die Eigenschaft Beim Laden sortie- ren in Bezug auf den in der Eigenschaft Sortiert nach festgelegten Sortierung.
Unterdatenblätter
Seit Access 2000 zeigen Tabellen in der Datenblatt- ansicht Unterdatenblätter an. Dies sieht wie in Bild 6 aus. Unterdatenblätter ermöglichen die Anzeige von Daten aus einer weiteren Tabelle oder Abfrage, die mit den Datensätzen der aktuellen Tabelle verknüpft
sind. So können Sie wie in der Abbildung alle Projekte eines Kunden direkt unterhalt der Kundendaten an- zeigen.Die Verhaltensweise von Unterdatenblättern steuern Sie mit den folgenden drei Eigenschaften:
• Unterdatenblattname: Legt fest, aus welcher Tabelle oder Abfrage die im Unterdatenblatt an- gezeigten Daten stammen. Wählen Sie die Ein- stellung [Automatisch], ermittelt Access eine Tabelle, die per Fremdschlüsselfeld mit dem Primärschlüsselfeld der aktuellen Tabelle ver- knüpft ist (also zum Beispiel tblProjekte und tblKunden über das in beiden Tabellen gleich- lautende Feld KundeID). Wählen Sie [Keines], zeigt Access schlicht kein Unterdatenblatt an. Sie können auch eine beliebige Tabelle oder Abfrage wählen. Wichtig ist in diesem Fall, dass Sie mit den Eigenschaten Verknüpfen von und Verknüp- fen nach festlegen, über welche Felder der bei- den Tabellen die Verknüpfung für das Unterda- tenblatt erfolgen soll.
• Unterdatenblatthöhe: Gibt die maximale Höhe des Unterdatenblatts je Datensatz an.
• Unterdatenblatt erweitert: Diese Eigenschaft legt fest, ob die Unterdatenblätter beim Öffnen des Datenblatts angezeigt werden oder nicht.
• Verknüpfen von: Mit dieser Eigenschaft legen Sie das Feld der aktuellen Tabelle fest, über das die Verknüpfung für das Unterdatenblatt erfolgen soll.
• Verknüpfen nach: Diese Eigenschaft gibt an, wel- ches Feld der unter Unterdatenblattname ange- gebenen Tabelle für die Anzeige der verknüpften Daten herangezogen wird.
Orientierung/Ausrichtung (2003)
Diese in Access 2010 in Ausrichtung umbenannte Eigenschaft wirkt sich in der deutschen Version von Access lediglich dadurch aus, dass der Text der Ti- telzeile einer Tabelle bei der Einstellung Von rechts nach links in der Datenblattansicht rechtsbündig an- gezeigt wird.
Sortierung aktiv (2010)
Über die Wirkung dieser noch neuen Eigenschaft schweigt sich die Onlinehilfe aus. Ändert man ihren Wert, wird dieser nach dem Wechseln in die Daten- blattansicht und wieder zurück wieder auf 0 gesetzt.
ke gehen (nach einiger Zeit kennen Sie sich mit den Ausdrücken wahrscheinlich auch so gut aus, dass Sie dies auf diese Weise erledigen werden).
Also schauen wir uns an, wie der Ausdrucks-Gene- rator beim Auffinden der benötigten Funktion hel- fen kann. Die drei Ordner im linken Listenfeld bie- ten die Grundlage für die Auswahl eines Ausdrucks, Der Ausdrucks-Generator ist an vielen
Stellen verfügbar – in Tabellen, Abfragen, Formularen, Berichten und Makros. Ob Sie den Ausdrucks-Generator zum For- mulieren eines Ausdrucks heranziehen können, erkennen Sie an der Schaltfläche mit den drei Punkten (...), die beim Aktivie- ren einer Eigenschaft rechts neben dieser erscheint. Aber Achtung: Bei manchen Eigenschaften aktiviert ein Klick auf diese Schaltfläche auch andere Assistenten, die beim Zuweisen eines Wertes helfen sol- len. Dies ist etwa bei der Eigenschaft Ein- gabeformat eines Tabellenfeldes in der Entwurfsansicht der Fall: Dort wird etwa der Eingabeformat-Assistent gestartet.
Statt durch einen Klick auf die Schaltfläche mit den drei Punkten können Sie den Aus- drucks-Generator auch mit der Tasten- kombination Strg + F2 öffnen (Umschalt + F2 öffnet das Zoom-Fenster).
Aktuelles Datum als Standardwert
Die Funktionsweise lässt sich am einfachsten an ei- nem Beispiel erläutern. Hier möchten wir als Stan- dardwert für ein Datumsfeld das aktuelle Datum festlegen. Das bedeutet, dass beim Anlegen eines neuen Datensatzes automatisch das aktuelle Datum für dieses Feld eingetragen werden soll.
Wie Bild 1 zeigt, ist die erste Voraussetzung für die- ses Feld gegeben: Beim Aktivieren der Eigenschaft Datum erscheint schon einmal die Schaltfläche mit den drei Punkten. Ein Klick öffnet dann auch prompt den Ausdrucks-Generator (s. Bild 2). Unter Access 2010 sieht der Ausdrucks-Generator übrigens etwas anders aus, auf die Änderungen gehen wir später ein.
Theoretisch könnten Sie den gewünschten Ausdruck nun einfach in das Feld oben eintragen. Wenn Sie genau wissen, welchen Inhalt Sie der Eigenschaft hinzufügen möchten, brauchen Sie den Ausdrucks- Generator jedoch gar nicht erst zu öffnen und können gleich im Textfeld der jeweiligen Eigenschaft zu Wer-
Der Ausdrucks-Generator
Ausdrücke schnell zusammenstellen mit einem praktischen Tool
Der Ausdrucks-Generator ist eines von vielen praktischen Helferlein, die gerade dem Einsteiger gute Diens- te erweisen können. Mit dem Ausdrucks-Generator stellen Sie Ausdrücke für verschiedene Eigenschaften zusammen.
Bild 1: Eigenschaften eines Datumsfeldes
Bild 2: Der Ausdrucks-Generator unter Access 2007 und jünger
der grundsätzlich aus einem oder mehreren Teil- ausdrücken besteht, die durch spezielle Operatoren verknüpft werden. Diese Operatoren fügen Sie ganz einfach über die Schaltflächen zwischen dem oberen Textfeld und den drei Listenfeldern ein.
Im vorliegenden Fall soll der Ausdruck jedoch nur das aktuelle Datum liefern. Im linken Listenfeld lan- den wir damit schnell im Ordner Funktionen, denn im Gegensatz zu Konstanten und Operatoren liefern nur Funktionen auch dynamische Werte wie etwa das Datum. Ein Doppelklick auf den Eintrag Funktionen öffnet den Unterordner Eingebaute Funktionen, der wiederum eine Reihe Einträge im mittleren und lin- ken Listenfeld erscheinen lässt (wenn Sie öffentliche VBA-Funktionen programmiert haben, finden Sie hier für manche Eigenschaften einen weiteren Ord- ner – dazu später mehr).
Klicken Sie im mittleren Ordner auf Datum/Uhrzeit und wählen Sie rechts den Eintrag Datum aus. Die- sen übertragen Sie entweder durch einen Klick auf die Schaltfläche Einfügen oder durch einen Doppel- klick auf diesen Eintrag ins obere Textfeld. Das Er- gebnis sieht schließlich wie in Bild 3 aus.
Nach dem Schließen des Ausdrucks-Generators mit der Schaltfläche OK finden Sie den Ausdruck in der Eigenschaft wieder. Wenn Sie in die Datenblattan- sicht der Tabelle wechseln und einen neuen Daten- satz anlegen, trägt Access dort automatisch das ak- tuelle Datum ein (siehe Bild 4).
Gültigkeitsregel mit dem Ausdrucks- Generator
Während Sie den Ausdruck Datum() schnell verinner- lichen werden, ist der Ausdrucks- Generator beim Anlegen eines Ausdrucks für die Gültigkeitsregel einer Tabelle schon hilfreicher. Hier kommt es schnell vor, dass man eines der Tabellenfelder referen- zieren möchte, aber vielleicht den Namen nicht mehr kennt. Legen wir also eine Gültigkeitsregel für die Tabelle tblProjekte an, die festlegt, dass das Startdatum vor dem End- datum liegen muss. Hierbei handelt es sich um eine Gültigkeitsregel auf Tabellenebene, nicht auf Feldebene.
Deshalb aktivieren Sie in der Ent- wurfsansicht der Tabelle das Ei- genschaftsfenster, klicken in die Eigenschaft Gültigkeitsregel und dann auf die Schaltfläche mit den drei Punkten (siehe Bild 5).
Bild 3: Die Funktion zum Einfügen des aktuellen Datums ist schnell gefunden.
Bild 4: Automatisches Datum für neue Datensätze
Bild 5: Erstellen einer Gültigkeitsregel mit dem Ausdrucks-Generator
Der Unterschied zum vorherigen Beispiel ist schnell zu erkennen: Im linken Listenfeld finden Sie einen neuen Eintrag mit dem Namen der Tabelle, für die Sie die Eigenschaft festlegen wollen. Ein Klick auf die- sen Eintrag zeigt alle Felder dieser Tabelle im mitt- leren Listenfeld an. Das linke enthält für jedes Feld den Eintrag <Wert>. Hier können Sie nun bequem die gewünschten Felder auswählen und die entspre- chenden Operatoren hinzufügen, also beispielsweise [Projektstart]<[Projektende] (siehe Bild 6).
Der Zugriff auf Felder der Tabelle mit der Gültigkeits- regel selbst ist das Höchste der Gefühle, was den Da- tenzugriff anbelangt. Gern würden Sie vielleicht auf einen bestimmten Wert der aktuellen oder einer an- deren Tabelle zugreifen, um den Standardwert et cete- ra zu füllen. Die dazu benötigten Domänenfunktionen (siehe Access-Funktionen – Teil I: Domänenfunktio- nen) sind hier jedoch nicht freigegeben, und auch be-
nutzerdefinierte Funktionen sind in den Eigenschaften von Tabellen und Feldern nicht verfügbar.
Ausdrucks-Generator in Abfragen, Formularen und Berichten
Auch in Abfragen, Formularen und Berichten sowie in den enthaltenen Steuerelementen gibt es einige Eigenschaften, die Sie mithilfe des Ausdrucks-Ge- nerators füllen können. Schauen wir uns zunächst ein Beispiel für eine Abfrage an (in Formularen, Be- richten und Steuerelementen klappt dies genauso).
Sie können den Ausdrucks-Generator hier an zwei Stellen öffnen: In der Spalte Feld oder in der Spalte Kriterien. Beim Definieren des anzuzeigenden Fel- dinhalts verwenden Sie den Ausdrucks-Generator meist dazu, ein sogenanntes berechnetes Feld zu de- finieren. Was das genau ist, erfahren Sie im Beitrag Abfragen für die Datenauswahl – Teil VI: Berechne- te Felder in Abfragen und Eigenschaften.
Wenn Sie den Ausdrucks-Generator für ein beliebi- ges Feld in der Zeile Kriterien öffnen, finden Sie die- sen wie in Bild 8 vor. Die linke Liste ist nun bereits deutlich besser gefüllt also noch beim Einstellen der Eigenschaften im Tabellenentwurf. Es gibt nun die folgenden Einträge:
• Das aktuelle Objekt, in diesem Falle die Abfrage qryAusdrucksGenerator mit seinen Feldern
• Listen aller Tabellen und Abfragen mit ihren Fel- dern
• Listen aller Formulare und Berichte mit allen Eigenschaften, allen Feldern der Datenherkunft Bild 6: Bei Eigenschaften auf Tabellenebene zeigt der Ausdrucks-Generator die Tabelle und ihre Felder an.
Bild 7: Den Ausdrucks-Generator öffnet man an man- chen Stellen auch über das Kontextmenü.
Bild 8: Für Abfragen, Formularen und Berichten bietet der Ausdrucks-Generator eine Reihe mehr Möglichkeiten.
Formulare aus. Schließlich finden Sie im mittleren Listenfeld alle Steuerelemente des Formulars so- wie die Einträge <Formular> und <Datenherkunft>.
<Formular> zeigt rechts alle Formulareigenschaften an, <Datenherkunft> offeriert eine Liste aller Felder der Datenherkunft. Wir möchten in diesem Fall den aktuellen Wert eines Kombinationsfelds ermitteln und verwenden dafür den Ausdruck aus Bild 9:
Formulare![frmProjekteMitKunde]![cboKundeID]
Sie könnten auch den größten Wert eines bestimmten Feldes in einer Tabelle benötigen. Wenn Sie in einem Textfeld eines Formulars beispielsweise den größten Wert eines Feldes in einer bestimmten Tabelle anzeigen möchten, markieren Sie im Formularentwurf zunächst das Feld, zeigen dann seine Eigenschaften an und öff- nen für die Eigenschaft Daten|Steuerelementinhalt den Ausdrucks-Generator (siehe Bild 10).
Wählen Sie hier links Funk tio- nen|Ein gebaute Funktionen aus, klicken Sie in der Mitte auf Domä- nenaggregat und klicken Sie dann rechts doppelt auf DomMax.
Im Textfeld oben erscheint dann der folgende Ausdruck:
DomMax («Ausdruck»; «Domäne»;
«Kriterien»)
Hier müssen Sie nun noch die Platzhalter «Ausdruck», «Domä- ne» und «Kriterien» ersetzen.
Auch das erleichtert der Aus- drucks-Generator: Sie brauchen und allen Steuerelementen. Diese
Listen liefern jeweils zwei Unterlisten, bei Formularen beispielsweise Gela- dene Formulare und Alle Formulare.
Geladene Formular sind alle aktuell geöffneten Formulare, egal in welcher Ansicht.
• Unter Funktionen einen weiteren Ein- trag, dessen Name dem VBA-Projekt zur aktuellen Datenbank entspricht.
• Eine Liste namens Gebräuchliche Ausdrücke, die jedoch vorrangig in Be- richten zum Einsatz kommen dürften und Funktionen wie Seitenzahl et cetera liefert.
Beispiele für Ausdrücke
Viele Ausdrücke, die Sie heute mit dem Ausdrucks- Generator zusammenstellen, um sie den Eigen- schaften der Access-Objekte zuzuweisen, werden Sie bald von Hand niederschreiben – und zwar sowohl als Werte von Eigenschaften als auch innerhalb von VBA-Prozeduren. Hier finden Sie einige Beispiele für solche Ausdrücke.
Wenn Sie beispielsweise in einer Abfrage ein Kriteri- um verwenden möchten, dass dem Feldinhalt eines aktuell geöffneten Formulars entspricht, wählen Sie im Ausdrucks-Generator zunächst den Eintrag For- mulare und dann, je nachdem, ob das betroffene For- mular geöffnet ist, den Namen des gewünschten For- mulars aus der Liste Geladene Formular oder Alle
Bild 9: Verweis auf ein Formular-Steuerelement
Bild 10: Verweis auf ein Formular-Steuerelement
nur einmalig auf den jeweiligen Platzhalter zu kli- cken, um diesen komplett zu markieren. Details zu den Domänenfunktionen finden Sie im Artikel Ac- cess-Funktionen – Teil I: Domänenfunktionen, wir zeigen hier nur die grundsätzliche Vorgehensweise beim Zusammenstellen einer solchen Funktion mit dem Ausdrucks-Generator:
Klicken Sie zuerst auf den ersten Parameter «Aus- druck». Dieser soll das Feld enthalten, dessen Werte Access nach dem größten Wert durchsuchen soll. Zu Beispielzwecken soll das Datum des neuesten Pro- jekts der Tabelle tblProjekte gefunden werden, also der größte Wert des Feldes Projektstart. Um dieses Feld statt des Platzhalters einzutragen, markieren Sie diesen und wählen dann in den Listenfeldern unten nacheinander die Werte Tabellen|tblProjekte, Pro- jektstart und <Wert> aus. Klicken Sie dann auf die Schaltfläche Einfügen, um diesen Wert in den Aus- druck einzufügen. Der Ausdruck sieht dann so aus:
DomMax ( [tblProjekte]![Projektstart] «Ausdr» ;
«Domäne»; «Kriterien»)
Leider können Sie mit diesem Ausdruck für den ers- ten Parameter «Ausdruck» nichts anfangen: Dort soll schlicht und einfach nur der Feldname landen, und zwar in Anführungszeichen eingefasst. An die- ser Stelle hilft der Ausdrucks-Generator also nur be- schränkt weiter. Tragen Sie die fehlenden Werte von Hand ein, was wie folgt aussieht:
DomMax ("Projektstart";"tblProjekte")
Diese Funktion soll den größten Wert des Feldes Pro- jektstart in der Tabelle tblProjekte liefern, was sie auch anstandslos erledigt. Als Wert der Eigenschaft Steuerelementinhalt eines Textfeldes erscheint das Ergebnis im betroffenen Textfeld.
Benutzerdefinierte Funktionen
Wenn Sie im VBA-Editor eine öffentliche Funktion wie die folgende in einem Standardmodul anlegen, können Sie diese ebenfalls über den Ausdrucks-Ge- nerator auswählen:
Public Function Test() As String Test = "Hallo"
End Function
Im Ausdrucks-Generator wählen Sie links Funktionen|<Projektname>, in der Mitte den Namen
des Standardmoduls und rechts den Funktionsna- men aus (siehe Bild 12). Das Ergebnis dieser VBA- Funktion wird beim Öffnen des Objekts an die ent- sprechende Eigenschaft übergeben.
Ausdrucks-Generator anpassen
Leider lässt sich der Ausdrucks-Generator nur be- schränkt anpassen, und das auch noch nicht einmal dauerhaft. Sie können zwar über die Ecke unten rechts die Größe des Fensters anpassen, aber beim nächsten Öffnen erscheint der Ausdrucks-Generator wieder in der Standardgröße. Dies ändert sich übrigens erst mit Access 2010 – dort wird die Größe gespeichert und beim nächsten Öffnen wieder hergestellt.
Ausdrucks-Generator unter Access 2010
Ab Access 2010 bietet der Ausdrucks-Generator we- sentliche Verbesserungen. Das liegt in erster Linie an der eingebauten IntelliSense-Funktion. Das bedeu- tet, dass der Ausdrucks-Generator beim Eingeben des ersten Buchstabens gleich alle aktuell verfügba- ren Vorschläge in einer Auswahlliste einblendet.
Bild 11: Ergänzen der Parameter einer Domänenfunktion
Bild 12: Ergänzen der Parameter einer Domänenfunktion
Der Oberbegriff für alle Felder in Abfra- geergebnissen, die nicht den Wert genau eines einzigen Feldes ausgeben, lautet
„Berechnete Felder“. Genaugenommen handelt es sich um Ausdrücke, die auf mehr als einem Feld basieren oder aber den Inhalt eines oder mehrere Felder durch eine Funktion verändert – natür- lich sind auch Kombinationen möglich.
Solche berechneten Felder gibt es unter anderem Namen auch in verschiedenen Eigenschaften von Formularen, Berichten und Steuerelementen oder auch als Krite- rien in Abfragen. Wir konzentrieren uns in diesem Artikel jedoch auf die Verwendung berechneter Felder zur Ermittlung der in einem Abfrageergebnis anzuzeigenden Daten.
Berechnete Felder
Der Schritt von einem einfachen Abfragefeld zu ei- nem berechneten Feld ist denkbar einfach: Sie brau- chen dem Feld einfach nur einen neuen Namen zu geben und den Feldinhalt beizubehalten. Wir greifen für diesen Artikel auf die Beispieldatenbank Süd- sturm.mdb zurück, die Sie im Downloadbereich fin- den – diese Datenbank liefert besseres Futter für die Beispiele dieses Artikels.
Erstellen Sie zunächst eine neue Abfrage (siehe Ab- frage erstellen) und fügen Sie die Tabelle tblBestell- details hinzu. Ziehen Sie alle Felder dieser Tabelle in das Entwurfsraster. Nun legen wir ein neues be- rechnetes Feld in der ersten leeren Spalte rechts an.
Es soll Preis heißen und den gleichen Inhalt wie das Feld Einzelpreis anzeigen. Dazu tragen Sie den fol- genden Ausdruck dort ein, wo sonst die Feldnamen stehen (siehe Bild 1): Preis: Einzelpreis
Ein Wechseln in die Datenblattansicht zeigt schnell, dass dieses Feld tatsächlich genau den gleichen In- halt wie das Feld Einzelpreis anzeigt – es hat sogar den gleichen Spaltennamen (siehe Bild 2).
Um aus diesem Feld ein richtiges berechnetes Feld zu machen, ersetzen wir den Ausdruck durch den folgenden:
Preis: [Einzelpreis]*[Anzahl]*(1-[Rabatt])
Wir möchten in dieser Spalte den Gesamtpreis für diese Position ermitteln, indem wir den Einzelpreis mit der Anzahl multiplizieren und außerdem den Ra- batt hinzuziehen. Die erste Auswirkung dieser Maß- nahme ist, dass in der Entwurfsansicht der Tabel- lenname aus der Zeile Tabelle verschwindet (siehe Bild 3).
Der Wechsel in die Datenblattansicht offenbart den zweiten Unterschied: Nun wird auch die vor dem Doppelpunkt angegebene Bezeichnung als Spalten-
Abfragen für die Datenauswahl
Teil VI: Berechnete Felder in Abfragen und Eigenschaften
Bislang haben wir in Abfragen nur die Inhalte einzelner Felder angezeigt, also beispielsweise Texte, Zahlen oder Datumsangaben. Sie können in einem Feld einer Abfrage aber auch gleich mathematische Berechnun- gen durchführen, Texte zusammenführen oder beliebige Feldinhalte mit eingebauten oder benutzerdefi- nierten Funktionen behandeln und das Resultat im Abfrageergebnis ausgeben. Wie das funktioniert, zeigen wir Ihnen in diesem Artikel.
Bild 1: Ein berechnetes Feld, das einfach nur umbenannt wurde und den Wert eines anderen Feldes anzeigen soll
Bild 2: Das berechnete Feld und das Originalfeld sehen genau gleich aus.
erkennen können, kann der Feldname eines berech- neten Feldes durchaus auch Leerzeichen und weiter Zeichen wie Umlaute, Bindestriche et cetera enthal- ten. Wir empfehlen jedoch, dies nicht zu tun und ei- nen Ausdruck wie KonstanterAusdruck zu verwen- den.
Soll die Abfrage in der Datenblattansicht eine ab- weichende Spaltenüberschrift anzeigen, legen Sie
diese einfach in der Eigenschaft Be- schriftung der Feldeigenschaften fest (siehe Bild 5).
Mathematische Funktionen
Access bietet eine Reihe mathematischer Funktionen an, die wir an anderer Stelle im Detail vorstellen.
Am einfachsten finden Sie diese Funktio- nen über den Ausdrucks-Generator, den wir im Artikel Der Ausdrucks-Generator genau beschreiben. Sie können sämtliche dieser Funktionen auch zur Definition be- rechneter Felder heranziehen.
kopf ausgegeben (siehe Bild 4). Außerdem liefert Access die richtige Berechnung für den Gesamtpreis dieser Position. Für die mathematische Berechnung brauchen Sie also nur die betroffenen Felder durch die entsprechenden Operatoren zu ver- knüpfen. Dabei können Sie auch Klam- mern verwenden, um die Reihenfolge der Berechnung anzupassen.
Halten wir also fest: Ein berechnetes Feld besteht aus dem neuen Feldnamen, ei- nem Doppelpunkt sowie dem eigentlich anzuzeigenden Ausdruck.
Immer aktuell
Berechnete Felder sind immer aktuell.
Wenn Sie also wie oben eine Berechnung auf zwei oder mehr Feldern durchführen lassen und während der Anzeige des Ergebnisses Änderungen an den zu- grunde liegenden Feldern vornehmen, werden diese Änderungen gleich in das Berechnungsergebnis über- nommen.
Einfachstes berechnetes Feld
Wenn Sie gedacht haben, die Angabe eines einfachen Feldes in einem berechneten Feld sei die einfachste Variante, haben Sie sich getäuscht: Sie können hier auch schlicht und einfach einen konstanten Aus- druck hinterlegen, beispielsweise wie folgt:
KonstanterAusdruck: 100
Die Abfrage liefert für das Feld Konstanter Ausdruck dann in jedem Datensatz den Wert 100. Wie Sie hier
Bild 3: Besteht das berechnete Feld aus mehr als nur einem Feld der Datenherkunft der Abfrage, verschwindet die Anzeige der Herkunftsta- belle.
Bild 4: Außerdem zeigt der Spaltenkopf nun die Bezeich- nung des berechneten Feldes an.
Bild 5: Anpassen des Spaltenkopfes eines berechneten Feldes
Im ersten Beispiel erstellen Sie eine Abfrage auf Ba- sis der Tabelle tblPersonal der Südwind-Datenbank.
Fügen Sie die drei Felder PersonalID, Vorname und Nachname zum Entwurfsraster hinzu und legen Sie dann ein neues Feld mit folgendem Ausdruck an (sie- he Bild 6):
Personal: [Vorname] & " " & [Nachname]
Was hier geschieht, ist eine ganz einfache Zeichen- verkettung. Access liest für jeden Datensatz des Ab- frageergebnisses den Wert des Feldes Vorname ein, fügt eine aus einem Leerzeichen bestehende Zei- chenkette hinzu und ergänzt den Ausdruck schließ- lich um den Wert des Feldes Nachname. Das Ergeb- nis ist in Bild 7 zu begutachten.
Für die zweite Variante verwenden wir den folgenden Ausdruck:
Personal: [Nachname] & ", " & [Vorname]
Außerdem lassen wir die beiden Felder Vorname und Nachname im Entwurfsraster weg. So erkennen Sie schnell, dass ein in einem berechneten Feld vorkom-
Zwischenrechnungen
Manchmal liefert ein berechnetes Feld nicht das erwartete Ergebnis. Vielleicht haben Sie eine Klammer vergessen oder nicht in der richtigen Reihenfolge gerech- net. Sie können solchen Problemen je- doch leicht auf den Grund gehen: Legen Sie einfach mehrere berechnete Felder an, in denen Sie Schritt für Schritt das ge- wünschte Endergebnis ermitteln. In obi- gem Beispiel könnten Sie beispielsweise zunächst den Einzelpreis mit der Anzahl multiplizieren:
PreisMalAnzahl: [Einzelpreis]*[Anzahl]
Danach ermitteln Sie den Rabatt:
PreisMalAnzahl: [Einzelpreis]*[Anzahl]
Schließlich verwenden Sie die beiden Zwischener- gebnisse, um das Endergebnis zu finden:
Endpreis: [PreisMalAnzahl]*[Rabattwert]
Texte „berechnen“
Das nächste Beispiel wird dem Verb „berechnen“
alles andere als gerecht: Wir fassen hier die Texte zweier oder mehrerer Felder in einem berechneten Feld einer Abfrage zusammen. Das beliebteste Bei- spiel ist das Zusammenführen von Vor- und Nachna- me von Personendaten. Diese sollen in der Regel in der Form <Vorname> <Nachname> oder als <Nach- name>, <Vorname> zusammengeführt werden. Bei- des ist mit einem entsprechend formulierten berech- neten Feld ganz einfach zu erledigen.
Bild 6: Zusammenfügen von Vor- und Nachname per berechnetem Feld
Bild 7: Das Ergebnis überzeugt!
Bild 8: Nachname und Vorname durch Komma getrennt
Der zweite fügt dann das Ergebnis des ersten Aus- drucks und den Nachnamen zusammen:
Briefanrede: [BriefanredeStart] & " " & [Nachname]
& ", "
Das Ergebnis aus Bild 10 überzeugt: Jede Person er- hält die richtige Anredefloskel. Da längere Ausdrü- cke in einer Zeile manchmal schwierig zu bearbeiten sind, können Sie diese auch im Zoom-Fenster anzei- gen. Betätigen Sie die Tastenkombination Umschalt + F2, öffnet sich wie in Bild 11 das Zoom-Fenster.
mendes Feld noch nicht einmal an anderer Stelle im Abfrageergebnis vorkommen muss (siehe Bild 8).
Sie sehen: Die Vorgehensweise ist ganz einfach. Sie brauchen nur die Feldnamen und die zusätzlichen Textteile durch das Kaufmanns- Und (&) zusammenzuführen.
VBA-Funktionen in
berechneten Abfragefeldern
In Abfragefeldern können Sie sowohl eingebaute als auch benutzerdefi- nierte VBA-Funktionen verwenden (mehr dazu unter Abfragen für die Datenauswahl – Teil VII: Benutzer- definierte Funktionen in Abfragen).
Bleiben wir gleich bei den Namen.
Wenn Sie Daten für ein Anschreiben zusammenstellen wollen, benötig- ten Sie auch eine entsprechende Anrede für die erste Textzeile, also beispielsweise Sehr geehrter Herr Müller. Die Tabelle tblPersonen der Südsturm-Beispieldatenbank liefert aber nur ein Feld namens
Anrede, das einen der beiden Werte Herr oder Frau enthält. Wie können wir daraus den gewünschten Anredetext inklusive Nachnamen ermitteln?
In diesem Fall verwenden wir die Wenn-Funktion.
Diese Funktion hat drei Parameter:
• Zu prüfender Ausdruck, zum Beispiel Anrede =
"Herr"
• Ausdruck, der zurückgegeben wird, wenn der zu prüfende Ausdruck den Wert True liefert
• Ausdruck der zurückgegeben wird, wenn der zu prüfende Ausdruck den Wert False liefert.
Zur besseren Übersicht erledigen wir die Aufgabe wie in Bild 9 in zwei Schritten, also mit Zwischener- gebnis. Der erste Ausdruck ermittelt zunächst einen der beiden Ausdrücke Sehr geehrter Herr oder Sehr geehrte Frau:
BriefanredeStart: Wenn([Anrede]="Herr";"Sehr geehr- ter Herr";"Sehr geehrte Frau")
Bild 9: Abfrage zum Zusammenstellen von Anredefloskeln
Bild 10: Die Anredefloskeln in der Datenblattansicht
Bild 11: Berechnetes Feld im Zoom-Fenster bearbeiten
Manchmal reichen die eingebauten VBA-Funktionen einfach nicht aus, um in einer Abfrage ein Feld mit den gewünschten Informationen zu füllen.
Im oben genannten Artikel gibt es ein Beispiel, in dem aus dem Einzelpreis, der Anzahl und dem Rabatt für eine Bestellposition der Gesamtpreis berechnet wird.
Dort verwenden wir gleich in ei- nem berechneten Abfragefeld eine entsprechende Berechnungsvor- schrift.
Da wir uns an dieser Stelle noch
nicht um komplizierte VBA-Funktionen kümmen möchten, wollen wir diese einfache Berechnung in eine VBA-Funktion auslagern.
Ergebnis einer VBA-Funktion in einer Abfrage
Wir starten jedoch zunächst mit dem einfachsten Fall: Die erste Beispielfunktion soll schlicht einen statischen Wert an die Abfrage zurückgeben. Die Funktion sieht wie folgt aus und wird in einem Stan- dardmodul namens mdlFunktionen der Südwind-
Beispieldatenbank angelegt (siehe VBA-Modul anle- gen und VBA-Routine schreiben):
Public Function Abfragewert() Abfragewert = 100
End Function
Auf diese Funktion greifen Sie in der Abfrage am ein- fachsten über den Ausdrucks-Generator zu (siehe auch Der Ausdrucks-Generator).
Wenn Sie den Namen der Funktion gerade im Kopf haben, brauchen Sie diesen allerdings gar nicht – tragen Sie einfach den Namen des Feldes sowie den Funktionsaufruf mit einem Klammernpaar in die Zei- le Feld der Abfrage ein:
Wert: Abfragewert()
Beachten Sie, dass die Abfrage mindestens eine Ta- belle als Datenherkunft haben muss, da diese sonst gar nicht erst in der Datenblattansicht angezeigt werden kann. Im Beispiel aus Bild 1 haben wir be- reits die Tabelle tblBestelldetails hinzugefügt. Aller- dings soll diese erste Beispielabfrage keine Felder der zugrunde liegenden Tabelle anzeigen, sondern nur das Ergebnis der Funktion Abfragewert.
Abfragen für die Datenauswahl
Teil VII: Benutzerdefinierte Funktionen in Abfragen
Wie Sie eingebaute VBA-Funktionen in berechneten Feldern von Abfragen einsetzen, haben Sie ja bereits unter Abfragen für die Datenauswahl - Teil VI: Berechnete Felder in Abfragen und Eigenschaften erfahren.
In diesem Artikel zeigen wir Ihnen nun, wie Sie selbst erstellte VBA-Funktionen in Access-Abfragen einset- zen.
Bild 1: Hinzufügen eines berechneten Felds auf Basis einer benutzerdefinier- ten VBA-Funktion
Bild 2: Das Ergebnis der Abfrage mit dem berechneten Feld
Wie Bild 2 zeigt, funktioniert die Abfrage mit der VBA-Funktion. Aber warum wird der Wert 100 so oft angezeigt und nicht nur einmal? Ganz einfach: Die Abfrage gibt immer soviele Datensätze aus, wie es die enthaltenen Tabellen vorgeben – egal, ob sie Fel- der der enthaltenen Tabellen anzeigt oder nicht.
Bevor wir uns dem nächsten Beispiel zuwenden, schauen wir uns etwas Interessantes an: Wenn die Abfrage einige hundert Datensätze enthält, wird die Abfrage dann eigentlich für jeden Datensatz einmal aufgerufen?
Um das herauszufinden, nutzen wir eine wichtige Funktion des VBA-Editors: den Haltepunkt. Öffnen Sie den VBA-Editor mit der soeben angelegten Funk- tion und klicken Sie einfach auf den grauen Rand links von der einzigen Anweisung der Funktion.
Wenn Sie nun die Abfrage qryEinfacheVBAFunkti- on in der Datenblattansicht öffnen, wechselt Access zum VBA-Editor und hinterlegt die betroffene VBA- Zeile gelb und hält die Ausführung des Codes an die- ser Stelle an (siehe Bild 3).
Wenn Sie nun auf F5 klicken, sollte die Funktion bis zum Erreichen des nächsten Haltepunkts fortgesetzt werden. Die Funktion wird beendet, aber der Halte- punkt kein weiteres Mal angesprungen – das heißt, dass die Funktion nur ein einziges Mal aufgerufen wird! Anscheinend merkt Access, dass die Funktion einen statischen Wert zurückliefert.
Berechnungen mit Parametern
Im zweiten Beispiel wollen wir einer Funktion die Werte der drei Felder Einzelpreis, Anzahl und Rabatt übergeben und daraus den Preis der Bestellposition ermitteln lassen.
Dazu erstellen Sie zunächst die folgende VBA-Funktion:
Public Function GesamtpreisBerechnen( _ curEinzelpreis As Currency, _ intAnzahl As Integer, _
curRabatt As Currency) As Currency GesamtpreisBerechnen = curEinzelpreis * _ intAnzahl * (1 - curRabatt)
End Function
Die Funktion erwartet beim Aufruf Werte für die Pa- rameter curEinzelpreis, intAnzahl und curRabatt.
Diese übergeben Sie beim Aufruf aus der Abfrage heraus durch Verweise auf die entsprechenden Fel- der des jeweiligen Datensatzes (siehe Bild 4):
Gesamtpreis: GesamtpreisBerechnen([Einzelpreis];
[Anzahl];[Rabatt])
Beim Anzeigen der Abfrage in der Datenblattansicht liest Access die Werte für den aktuellen Datensatz aus, ruft die VBA-Funktion mit diesen Parametern auf und gibt schließlich die ermittelten Werte aus.
Hierbei ist zu beachten, dass die VBA-Funktion tat- sächlich für jeden Datensatz aufgerufen wird. Aber nicht auf einen Schlag! Die VBA-Funktion wird zu-
nächst nur für die aktuell sichtba- ren Datensätze ausgeführt. Dies können Sie ebenfalls durch Setzen eines Haltepunktes feststellen.
Einfacher ist es jedoch, folgende Zeile in die Funktion einzufügen:
curEinzelpreis * intAnzahl * (1 - curRabatt)
Der VBA-Editor zeigt dann die Pa- rameterwerte der bis dato behan- delten Datensätze im Direktfenster an.
Bild 3: Mit einer Haltemarke stellen wir fest, ob und wann die VBA-Funktion aufgerufen wird.
Bild 4: Entwurf der Abfrage mit dem Aufruf der VBA-Funktion mit Parametern
Domänenfunktionen ermöglichen den Zugriff auf genau einen Wert einer Datensatzgruppe, also eines oder mehrere Datensätze einer Tabelle oder Abfrage.
Sie können damit beispielsweise folgende Informati- onen abfragen:
• Vorname des Kunden mit der Kunden-ID 12345 aus der Tabelle tblKunden
• Anzahl der Artikel aus einer Tabelle tblArtikel, die teurer als 100 Euro sind
• Preis des teuersten Artikels der Tabelle tblArti- kel
• Durchschnittspreis aller Artikel der Tabelle tbl- Ar tikel
Einsatzorte
Domänenfunktionen und Access-Funktionen im all- gemeinen setzen Sie beispielsweise an folgenden Stellen ein:
• Feldinhalt von Abfragen
• Kriterium von Abfragen
• Steuerelementinhalt von Steuerelementen in Formularen oder Berichten
Grundlagen
Grundsätzlich sind Domänenfunktionen mit Abfra- gen vergleichbar, die jedoch eine entscheidende Einschränkung aufweisen müssen: Sie dürfen das Ergebnis in nur einen einzigen Datensatz mit einem einzigen Feld zurückliefern.
Der Begriff Domänenfunktion wird vom englischen Domain abgeleitet. Domain bezieht sich im Daten- bankbereich auf alle Werte eines Feldes einer Tabel- le oder Abfrage. Die Domänenfunktionen erlauben
es, die Werte dieser „Domäne“ auf verschiedene Ar- ten abzufragen.
Für alle Domänenfunktionen gibt es eine englische Version, die grundsätzlich unter VBA zum Einsatz kommt, sowie eine Version in der jeweiligen An- wendungssprache. Diese Variante verwenden Sie in jeglichen Eigenschaften, die Sie über die Benutzer- oberfläche von Access in den verschiedenen Ent- wurfsansichten einstellen.
Sie können den Namen einer Domänenfunktion als Feldwert, Kriterium oder Eigenschaftswert zwar auch in Englisch angeben – Access benennt den Namen allerdings direkt nach dem Eingeben in die Anwendungssprache um. Es gibt noch einen weite- ren wesentlichen Unterschied bei der Eingabe von Access-Funktionen in VBA-Routinen auf der einen und Feldwerten, Kriterien und Eigenschaften auf der anderen: Unter VBA trennen Sie die Parameter durch Kommata voneinander, sonst durch Semikola.
Domänenfunktionen im Überblick
Access stellt insgesamt zwölf Funktionen zur Ver- fügung, die allesamt mit „D“ beginnen. Die folgende Auslistung liefert die deutsche Bezeichnung inklusi- ve Syntax, die englische Bezeichnung mit Syntax und eine Erläuterung. Für alle Domänenfunktionen gilt:
Sie berücksichten nur solche Datensätze, in denen der unter Ausdruck angegebene Wert nicht NULL ist, das Feld beziehungsweise die Felder müssen also ei- nen Wert enthalten. Außerdem werden natürlich nur solche Datensätze berücksichtigt, die dem unter Kri- terium angegebenen Ausdruck entsprechen:
• DomAnzahl(Ausdruck;Domäne[;Kriterium]
DCount(Ausdruck,Domäne[,Kriterium]) Liefert die Anzahl der Datensätze.
• DomErsterWert(Ausdruck;Domäne[;Kriterium]
DFirst(Ausdruck,Domäne[,Kriterium]) Liefert den Feldinhalt des ersten Datensatzes.
Access-Funktionen
Teil I: Domänenfunktionen
In der Reihe Access-Funktionen stellen wir Ihnen in Access eingebaute Funktionen vor, die Sie sowohl in Eigenschaften von Tabellen, Abfragen, Formularen und Berichten als auch in VBA-Routinen einsetzen kön- nen. Im ersten Teil dieser Artikelreihe beschäftigen wir uns mit den sogenannten Domänenfunktionen.
Auch wenn die Bezeichnung nicht bei jedem gleich die richtige Assoziation auslöst: Hier geht es um Funkti- onen, mit denen Sie auf die in Tabellen gespeicherten Daten zugreifen können.
• DomLetzterWert(Ausdruck;Domäne[;Kriteri um])
DLast(Ausdruck,Domäne[,Kriterium]) Liefert den Feldinhalt des letzten Datensatzes.
• DomMax(Ausdruck;Domäne[;Kriterium]) DMax(Ausdruck,Domäne[,Kriterium]) Liefert den maximalen Wert eines Feldes.
• DomMin(Ausdruck;Domäne[;Kriterium]) DMin(Ausdruck,Domäne[,Kriterium]) Liefert den minimalen Wert eines Feldes.
• DomMittelwert(Ausdruck;Domäne[;Kriterium]) DAvg(Ausdruck,Domäne[,Kriterium])
Liefert den Durchschnittswert des Feldinhaltes.
Nullwerte werden ignoriert.
• DomStAbw(Ausdruck;Domäne[;Kriterium]) DStDev(Ausdruck,Domäne[,Kriterium]) Liefert die Standardabweichung eines Feldes, bezogen auf eine Stichprobe.
• DomStAbwn(Ausdruck;Domäne[;Kriterium]) DStDevP(Ausdruck,Domäne[,Kriterium]) Liefert die Standardabweichung eines Feldes, bezogen auf die Gesamtmenge.
• DomSumme(Ausdruck;Domäne[;Kriterium]) DSum(Ausdruck,Domäne[,Kriterium]) Liefert die Summe der Feldinhalte.
• DomVarianz(Ausdruck;Domäne[;Kriterium]) DVar(Ausdruck,Domäne[,Kriterium])
Liefert die Varianz eines Feldes, bezogen auf eine Stichprobe.
• DomVarianzen(Ausdruck;Domäne[;Kriterium]) DVarP(Ausdruck,Domäne[,Kriterium])
Liefert die Varianz eines Feldes, bezogen auf die Gesamtmenge.
• DomWert(Ausdruck;Domäne[;Kriterium]) DLookup(Ausdruck,Domäne[,Kriterium]) Liefert den Feldinhalt des ersten Datensatzes.
Beispieldatenbank
Die Beispiele zu diesem Artikel finden Sie in der Bei- spieldatenbank Südsturm.mdb.
Domänenfunktionen einsetzen
Zum einfachen Ausprobieren der Domänenfunktio- nen erstellen Sie zunächst ein einfaches Formular und fügen diesem ein Textfeld hinzu. Nennen Sie dieses txtDCount und weisen Sie seiner Eigenschaft Steuerelementinhalt den folgenden Ausdruck zu (siehe Bild 1):
=DomAnzahl("ArtikelID";"tblArtikel")
Sie sehen hier bereits, dass die Bezeichnungen von Feldern und Tabellen/Abfragen normalerweise in Anführungszeichen eingefasst und somit als Zei- chenkette übergeben werden. Das gilt übrigens auch für das Kriterium.
Es gibt jedoch auch Ausnahmen, zum Beispiel wenn ein Parameter in einem Steuerele- ment oder einer Variablen enthal- ten ist – dazu später mehr.
Wenn Sie in die Formularansicht wechseln, zeigt das Textfeld die Anzahl der Datensätze der Tabelle tblArtikel an.
Dieser Funktionsaufruf liefert die Anzahl aller Datensätze der Tabelle tblArtikel, deren Feld ArtikelID ei- nen Wert enthält. Da ArtikelID das Primärschlüsselfeld der Tabelle ist, enthält jeder Datensatz einen Wert für dieses Feld. Die DomAnzahl- Funktion liefert also die Gesamtzahl der enthaltenen Datensätze zurück.
Bild 1: Textfeld mit einer Domänenfunktion als Steuerelementinhalt
Pflichtprogramm
Sie müssen für jede Domänenfunktion zumindest die ersten beiden Parameter übergeben. Der Parameter Ausdruck ent- hält eine der folgenden Möglichkeiten:
• Feldname: Gibt das auf dieses Feld bezogene Ergebnis der jeweiligen Do- mänenfunktion zurück.
• Ausdruck: Sie können auch einen be- liebigen, aus mehreren Feldnamen und Operatoren zusammengesetzten Ausdruck angeben.
• Sternchen (*): Nur für die DomAn- zahl-Funktion. Legt fest, das alle Da- tensätze gezählt werden und nicht nur diejenigen, die für das statt des Stern- chens angegebene Feld einen Wert enthalten.
Für Domäne geben Sie den Namen einer beliebigen Tabelle oder Abfrage an.
Kriterium verwenden
Im ersten Beispiel haben wir noch kein Kriterium angegeben. Ändern Sie den Wert der Eigenschaft Steuerelementin- halt des Textfeldes nun auf den folgenden Ausdruck:
=DomAnzahl("*";"tblArtikel";"Einzelpreis < 10") Dies liefert die Anzahl aller Datensätze der Tabelle tblArtikel, deren Preis kleiner als zehn Euro ist.
Als Kriterium können Sie genau die gleichen Ausdrü- cke angeben, die Sie in Abfragen verwenden – also beispielsweise wie in Abfragen für die Datenauswahl - Teil III: Filtern von Textfeldern oder Abfragen für die Datenauswahl - Teil V: Filtern nach Zahlen.
Fasst man die drei Kriterien zusammen, könnte man diese auch als SQL-Abfrage formulieren, schema- tisch also etwa wie die Abfrage SELECT Ausdruck FROM Domäne WHERE Kriterium.
Dabei gilt die bereits erwähnte Einschränkung, dass das Ergebnis nur einen einzigen Wert zurückliefern darf.
Beispielformular erweitern
Als nächstes erweitern wir das Beispielformular so, dass Sie ein wenig mit den verschiedenen Domänen- funktionen experimentieren können. Dazu fügen Sie zunächst drei Textfelder mit folgenden Namen zum Formular hinzu:
• txtAusdruck
• txtTabelleAbfrage
• txtKriterium
Danach ändern Sie die Eigenschaft Steuerele- mentinhalt des Textfeldes txtDomAnzahl erneut ab, und zwar so:
=DomAnzahl(txtAusdruck;txtTabelleAbfrage;
txtKriterium)
Bild 2: Parameter und Ergebnisse der Domänenfunktionen im For- mular
Bild 3: Ergebnisse aller Domänenfunktionen im Überblick
Wichtig ist an dieser Stelle, dass Sie die Namen der drei Textfelder nicht in Anführungszeichen einfassen.
Der Genauigkeit halber müssten Sie den Bezeich- nungen eigentlich sogar noch ein Me! voranstellen, damit klar ist, dass sich der Ausdruck auf Steuerele- mente des Formulars handelt, in dem auch der Aus- druck verwendet wird.
Wenn Sie nun in die Formularansicht wechseln und die Werte des vorherigen Beispiels in die drei oberen Textfelder eintragen, zeigt das untere Textfeld gleich das richtige Ergebnis an (siehe Bild 2).
Dieses Formular erweitern Sie nun, indem Sie für alle zwölf Domänenfunktionen ein eigenes Textfeld mit einem entsprechenden Aufruf der jeweiligen Do- mänenfunktion anlegen. Das fertige Formular finden Sie in der Beispieldatenbank Südwind.mdb unter dem Namen frmDomaenenfunktionen (siehe Bild 3).
Beispiele für verschiedene Ausdrücke
Am Beispiel der Tabelle tblPersonal zeigen wir, wie Sie den Parameter Ausdruck von Domänenfunk- tionen gestalten können. Wenn Sie einfach nur den Vornamen des ersten Mitarbeiters aus der Tabelle tblPersonal ermitteln möchten, reichen die Angabe des Feldes und der Tabelle aus:
DomWert("[Vorname]";"[tblPersonal]")
Vielleicht möchten Sie aber auch gleich Vor- und Nachname zusammensetzen:
DomWert("[Vorname] & " " &
[Nachname]";"[tblPersonal]")
Dies gelingt auch für mathematische Berechnungen.
Folgendes Beispiel gibt schlicht den Einzelpreis für eine Bestellposition aus:
DomWert("[Einzelpreis]";"[tblBestelldetails]") Wenn Sie hingegen den Gesamtpreis der Bestellpo- sition ermitteln möchten, bringen Sie folgende For- mel in der Funktion unter:
DomWert("[Einzelpreis] * [Anzahl] * (1-[Rabatt])";"
[tblBestelldetails]")
Wenn Sie innerhalb eines Ausdrucks Anführungszei- chen ausgeben möchten, tragen Sie an der entspre-
chenden Stelle ein doppeltes Anführungszeichen ein ("").
Beispiele für Domänen beziehungsweise Tabellen/Abfragen
Dieser Parameter ist einfach: Sie dürfen schlicht und einfach nur Namen von Tabellen oder gespeicherten Abfragen der lokalen Datenbank angeben. Auch ver- knüpfte Tabellen können verwendet werden. Es ist nicht erlaubt, statt einer Abfrage etwa einen SQL- Ausdruck wie SELECT * FROM tblArtikel als Domäne anzugeben.
Beispiele zum Kriterium
Der letzte Parameter der Domänenfunktionen schränkt die zu untersuchenden Datensätze ein.
Wenn Sie beispielsweise den Namen eines bestimm- ten Mitarbeiters ermitteln wollen, geben Sie ein Kri- terium wie das folgende an:
DomWert("[Vorname]";"[tblPersonal]";
"PersonalID = 1")
Sie erhalten hiermit nur den Vornamen zum Datensatz mit dem Wert 1 im Feld PersonalID.
Vielleicht möchten Sie aber auch Informationen zu ei- ner speziellen Gruppe von Datensätzen erhalten. Die Anzahl der Artikel mit einem Einzelpreis größer als 10 Euro erhalten Sie beispielsweise mit dieser Domänen- funktion:
DomAnzahl("[ArtikelID]";"[tblArtikel]";
"[Einzelpreis] > 10")
Besonderheiten ergeben sich beispielsweise bei De- zimalzahlen. Wenn Sie im vorherigen Beispiel etwa keinen runden Euro-Betrag als Vergleichswert wün- schen, sondern beispielsweise 5,50 Euro, würden Sie vielleicht das Kriterium [Einzelpreis] > 5,5 einset- zen. In der Tat führt dies jedoch zu einem Fehler, weil Sie als Dezimaltrennzeichen einen Punkt verwenden müssen:
DomAnzahl("[ArtikelID]";"[tblArtikel]";
"[Einzelpreis] > 5.5")
Auch der Einsatz einer Zeichenkette birgt Fallstricke:
Sie müssen einen Vergleichswert hier nämlich in Hochkommata oder doppelte Anführungszeichen ein fassen. Wenn Sie einen Mitarbeiter ermitteln möch-
ten, dessen Vorname mit A beginnt, formulieren Sie die Domänenfunktion wie folgt:
DomWert("[Vorname]";"[tblPersonal]";
"[Vorname] LIKE 'A*'")
Interessant ist auch der Einsatz von Datumsangaben als Kriterium. Wenn Sie etwa auf alle Bestellungen zugreifen möchten, die nach einem bestimmten Da- tum stattgefunden haben, formulieren Sie das nicht etwa so:
DomWert("*";"[tblBestellungen]";"[Bestelldatum] >
1.1.2011")
Die Domänenfunktion erwartet die Angabe des Da- tums nämlich im Format yyyy/mm/dd:
DomWert("*";"[tblBestellungen]";"[Bestelldatum] >
2011/1/1")
Normalerweise schreiben Sie das Kriterium aber nicht fest in die Funktion, sondern lesen es etwa aus einem Textfeld aus. Da dort ein Ausdruck wie 1.1.2011 steht, müssen Sie diesen eigentlich noch in das richtige Format bringen.
Wenn Sie das Feld mit dem Datum jedoch jedoch di- rekt referenzieren, funktioniert es auf die folgende Weise:
DomWert("BestellungID";"[tblBestellungen]";
"[Bestelldatum] > Forms!frmDomaenenfunktionen!
[txtTestfeld]")
Alles aus einem Datensatz
Die Funktionen DomWert, DomErsterWert, Dom- LetzterWert, DomMax und DomMin ermitteln ihre Werte jeweils aus einem einzigen Datensatz. Dies gilt auch, wenn das Kriterium eigentlich mehrere Daten- sätze zurückliefern würde.
Die Funktionen DomWert und DomErsterWert lie- fern übrigens identische Werte, da sie sich immer auf den ersten Datensatz der Datensatzgruppe beziehen.
Falls eine dieser Funktionen keinen passenden Da- tensatz findet, liefert sie den Wert Null zurück. Wenn das Kriterium nur einen Datensatz zurückliefert, was beispielsweise beim Verwenden des Primärschlüs- selfeldes der Fall ist (zum Beispiel PersonalID = 1),
liefern alle fünf Funktionen genau diesen Datensatz zurück.
Unterschiede zwischen den Funktionen ergeben sich erst, wenn das Kriterium mindestens zwei Daten- sätze zurückliefert. In diesem Fall liefern DomWert und DomErsterWert Daten des ersten Datensatzes, DomLetzterWert referenziert den letzten gefunde- nen Datensatz und DomMax und DomMin sortieren das Ergebnis nach dem im Ausdruck festgelegten Wert.
Alles aus allen Datensätzen
Anders sieht es beiden Funktionen DomAnzahl, DomMittelwert, DomStAbw, DomStAbwn, Dom- Summe, DomVarianz und DomVarianzen aus.
Diese Funktionen beziehen sich immer auf alle Da- tensätze, die erstens dem Kriterium entsprechen und deren Ausdruck einen anderen Wert als Null zu- rückliefert.
Wenn Sie also den durchschnittlichen Preis aller Ar- tikel ermitteln wollen, dann werden nur solche Ar- tikel berücksichtig, für die auch ein Preis hinterlegt ist.
Für die Ermittlung etwa des Mittelwerts ist dies un- abdingbar – eine Berücksichtigung von Nullwerten würde das Ergebnis verfälschen. Der Nullwert ist übrigens nicht mit dem Zahlenwert 0 zu verwech- seln: 0 wird durchaus in der Berechnung der Werte von Domänenfunktionen wie DomMittelwert berück- sichtigt.
Wenn eine diese Domänenfunktionen auf eine leere Datensatzmenge stößt, gibt sie einen Nullwert zu- rück (das gilt nicht für die Funktion DomAnzahl – die liefert natürlich den Wert 0).
Die oben genannten Domänenfunktionen liefern Er- gebnisse mathematischer Berechnungen und sind somit nur für Zahlenfelder einsetzbar.
Die beiden statistischen Domänenfunktionen Dom- Varianz und DomStAbw ermitteln ihre Ergebnisse aus einer Stichprobe und nicht wie ihre Pendants DomVarianzen und DomStAbwn aus der Gesamtheit der Datensätze. Sie benötigen daher mindestens zwei Datensätze in der Ergebnismenge, um ein an- deres Resultat als einen Nullwert zu liefern.
Es könnte nämlich beispielsweise passieren, dass ein Feldname ein Minuszeichen enthält. Access wür- de dies als Rechenzeichen interpretieren.
Bei #Fehler
Früher oder später werden Sie auf den Ausdruck
#Fehler als Ergebnis eines Domänenfunktion stoßen.
In vielen Fällen findet man nicht auf den ersten Blick heraus, wo der Fehler liegt. Sie können dann folgen- den Trick durchführen: Kopieren Sie die Domänen- funktion und fügen Sie diese im Direktfenster des VBA-Editors ein. Ersetzen Sie den Namen der Funk- tion durch die englische Variante, bei DomWert also beispielsweise DLookup (siehe Auflistung oben). Er- setzen Sie außerdem die Semikola durch Kommata.
Wenn Sie die Funktion dann mit vorangestelltem De- bug.Print beziehungsweise ? ausführen, erscheint eine Fehlermeldung wie in Bild 4.
Performance von Domänenfunktionen
Domänenfunktionen sind nicht ge- rade als Boliden bekannt. Sie sind zwar einfach und flexibel einsetz- bar, führen aber oft zu erheblichen Performanceverlusten.
Besonders bei der Verwendung in- nerhalb einer Abfrage kann es bei unüberlegtem Einsatz dieser Funk- tionen zu inakzeptablen Geschwin- digkeitseinbußen kommen.
Wenn Sie Domänenfunktionen einsetzen, müssen Sie sich darüber im Klaren sein, daß jede dieser Funkti- onen eine eigene Abfrage darstellt, die über die darin festgelegte Domäne ausgeführt wird.
Je nach der Definition der Abfrage, in der eine Do- mänenfunktion verwendet wird, kann es daher sein, dass die Abfrage der Domänenfunktion für jeden Da- tensatz der eigentlichen Abfrage erneut ausgeführt werden muss. Oft gibt es jedoch andere Alternativen, die in wesentlich kürzerer Zeit zum gleichen Ergeb- nis führen.
Auf das Thema Performance kommen wir jedoch später in einem anderen Artikel zu sprechen.
Tipps zu Domänenfunktionen
Sie sollten Tabellen- und Feldnamen in Domänen- funktionen grundsätzlich in eckige Klammern ein- fassen. So laufen Sie keine Gefahr, dass Access einen Namen fehlinterpretiert.
Bild 4: VBA liefert aussagekräftige Fehlermeldungen.
