<\/p>\n
![\"Beispiel-Suchformular\"](\"..\/fileadmin\/_temp_\/2021_01\/pic_1281_001.png\")
<\/p>\n
Bild 1: Beispiel-Suchformular<\/span><\/b><\/p>\n Wenn Sie per VBA SQL-Suchkriterien zusammenstellen, also den Teil einer SQL-Abfrage, der mit WHERE beginnt, ist das in der Regel eine Menge Schreibarbeit. Eine Menge Schreibarbeit geht oft einher mit Fehleranfälligkeit. Sie benötigen Code, um zu schauen, ob überhaupt ein Vergleichswert eingegeben wurde, müssen dann die verschiedenen Vergleichsausdrücke zusammenstellen, diese mit OR oder AND verknüpfen, dabei unterschiedliche Datentypen beachten und davon abhängig Hochkommata setzen oder auch Datumsangaben konvertieren. All dies können Sie sich sparen, wenn Sie ein paar einfache Hilfsfunktionen einsetzen.<\/b><\/p>\n Wenn Sie ein Suchformular mit Steuerelementen zur Eingabe mehrerer Kriterien ausstatten (wie in Bild 1), müssen Sie diese auch auswerten. Das geschieht normalerweise per VBA.<\/p>\n Bild 1: Beispiel-Suchformular<\/span><\/b><\/p>\n In einer Prozedur, die beispielsweise durch eine Suchen-Schaltfläche ausgelöst wird, stehen dann Anweisungen wie die aus Listing 1.<\/p>\n Listing 1: Zusammensetzen einer Where-Bedingung<\/span><\/b><\/p>\n Hier deklarieren wir zu Beginn eine Variable namens strWhere<\/b>, welche den Inhalt der WHERE<\/b>-Klausel einer SELECT<\/b>-Abfrage aufnehmen soll. Diese werden dann später zusammengesetzt und beispielsweise einem Unterformular oder einem Listenfeld als Datenquelle zugewiesen (über die Eigenschaft Recordsource <\/b>oder RowSource<\/b>).<\/p>\n In unserem Beispiel haben wir einige Felder, die einfache Texte abfragen wie txtSucheNachFirma<\/b>. Andere fragen nach einem Datum wie txtSucheNachGeburtsdatum<\/b> oder nach Zahlenwerten (txtSucheNachKreditscoreBis<\/b>).<\/p>\n Für alle Suchsteuerelemente prüfen wir zunächst, ob dieses überhaupt einen Wert enthält (Len(Nz( Im ersten Fall fügen wir direkt einen Ausdruck hinzu, der mit AND <\/b>beginnt, dann den Namen des zu untersuchenden Feldes (Firma<\/b>), den Vergleichsoperator (LIKE<\/b>) und den Vergleichswert (der Inhalt von txtSucheNachFirma <\/b>in Hochkommata). Frage: Warum beginnen wir gleich den ersten Ausdruck mit AND<\/b>, obwohl wir diesen Teil später, wenn wir diesen an WHERE <\/b>anhängen, sowieso entfernen müssen Der Grund ist einfach: Wenn wir allen Elementen der Where<\/b>-Klausel ein AND <\/b>voranstellen, können wir auch davon ausgehen, dass bei Vorhandensein mindestens eines Ausdrucks auch ein AND<\/b> ganz vorn steht – und dieses dann einfach abschneiden.<\/p>\n Vergleichsausdrücke mit Datum sehen etwas anders aus. Der Grundaufbau ist zwar gleich, aber wir verwenden hier eine Funktion, um den Inhalt des Datumsfeldes, zum Beispiel 31.12.2020<\/b>, in einen universell einsetzbaren SQL-Datumsausdruck umzuwandeln, in diesem Fall #2020\/12\/31 00:00:00#<\/b>. Dazu verwenden wir die Hilfsfunktion ISODatum<\/b>, die Sie im Modul mdlTools <\/b>finden:<\/p>\n <\/p>\n Beim Datum geben wir im Gegensatz zum Auch hier stellen wir dem Teilausdruck ein AND <\/b>voran und fügen diesen dann zum bereits vorhandenen Ausdruck strWhere <\/b>hinzu. Wollen wir mit Zahlenfeldern vergleichen, benötigen wir keine Hochkommata, aber ähnlich wie bem Datum müssen wir berücksichtigen, dass die Zahlenwerte ein Komma enthalten können. Unter SQL wird aber immer der Punkt als Dezimaltrennzeichen verwendet. Das heißt, dass wir die Replace<\/b>-Prozedur nutzen, um ein eventuell vorhandenes Komma durch einen Punkt als Dezimaltrennzeichen ersetzen:<\/p>\n Schließlich müssen wir noch Kontrollkästchen betrachten. Je nach Anwendungsfall kann ein Kontrollkästchen in der deutschen Version Werte wie Ja\/Nein oder Wahr\/Falsch liefern. Wenn Sie diese Werte unbehandelt in einen Where-Ausdruck einfließen lassen, löst dies einen Fehler aus – Access-SQL kennt nämlich Ja<\/b>, Nein<\/b>, Wahr <\/b>oder Falsch <\/b>nicht als gültige Werte an. Dementsprechend liefert ein Ausdruck wie Newsletter = Ja <\/b>einen Fehler. Das ist aber kein Problem, denn eine Umwandlung in den Datentyp Integer mit der Funktion CInt schreibt den Wert als -1 (Wahr) oder 0 (Falsch) in den Ausdruck:<\/p>\n Schließlich prüfen wir am Ende, ob strWhere <\/b>überhaupt eine Länge größer als 0 <\/b>hat. Falls ja, wissen wir auch, dass die ersten fünf Buchstaben ein Leerzeichen, die Zeichenkette AND <\/b>und noch ein Leerzeichen sind. Damit können wir dann einfach mit Mid(strWhere, 6) <\/b>nur den Inhalt ab dem sechsten Zeichen weiterverwenden und diesem noch das WHERE<\/b>-Schlüsselwort voranstellen:<\/p>\n Dies ergibt dann beispielsweise einen Ausdruck wie den folgenden:<\/p>\n Um diesen Ausdruck zu erstellen, mussten wir allerdings eine ganze Menge beachten. An einer Stelle gab es bereits eine kleiner Optimierung, die wir in Access im Unternehmen immer nutzen, wenn es nötig ist – die Umwandlung des Datums mit der Funktion ISODatum<\/b>. Nun wollen wir uns um den Rest kümmern und die Erstellung des Where-Ausdrucks wesentlich vereinfachen.<\/p>\n Wenn Sie einen Vergleich für ein Textfeld mit einer Zeichenkette anstellen möchten, müssen Sie den Vergleichswert entweder in Hochkommate (‚Beispiel‘<\/b>) oder doppelte Anführungszeichen („“Beispiel““<\/b>) einfassen. Der Grund ist, dass ja bereits der Ausdruck selbst in Anführungszeichen eingefasst ist und ein einfaches Anführungszeichen innerhalb der Zeichenkette das Ende der Zeichenkette signalisieren würde, was durch die nachfolgenden Zeichen einen Fehler auslöst. Unsere Vereinfachung soll dem Programmierer das Setzen von Hochkommata abnehmen.<\/p>\n Die Abfrage, ob das Steuerelement überhaupt einen Wert enthält, müssen wir für jedes Steuerelement durchführen. Auch diese sollte in unserer Vereinfachung enthalten sein, sodass der Benutzer nur noch einen Verweis auf das Steuerelement übergeben muss.<\/p>\n Außerdem wollen wir dem Benutzer die Unterscheidung verschiedener Datentypen weitgehend abnehmen. Bisher muss er bei Zeichenketten die Hochkommata setzen, bei Datumsangaben die Konvertierung mit ISODatum <\/b>vornehmen, bei Zahlenwerten die Kommata durch Punkte und bei Booleans Wahr <\/b>und Falsch <\/b>durch -1 <\/b>und 0 <\/b>ersetzen. Hier können wir, wie Sie gleich sehen werden, allerdings nicht ohne größeren Aufwand zwischen Zahlen und Datumsangaben unterscheiden.<\/p>\n Die resultierende Prozedur soll nun die folgenden Parameter entgegennehmen:<\/p>\n Die Prozedur aus Listing 2 rufen Sie beispielsweise mit der folgenden Anweisung auf. Dabei ist strWhere <\/b>eine Variable, in der Sie ein oder mehrere Filterkriterien zusammenstellen wollen. Firma <\/b>ist das Vergleichsfeld der zu untersuchenden Datenquelle, „LIKE“ <\/b>der Vergleichsoperator, Me!txtSucheNachFirma <\/b>das Feld, das den Vergleichswert enthält und „AND“ <\/b>ist der Operator, mit dem der neue Ausdruck mit bereits in strWhere <\/b>vorhandenen Ausdrücken verknüpft werden soll:<\/p>\n Listing 2: Prozedur zum Hinzufügen einer Bedingung<\/span><\/b><\/p>\n Die Prozedur prüft, ob strCondition <\/b>bereits einen Vergleichsausdruck enthält. Falls nein, wird strAndOr <\/b>gleich mit einer leeren Zeichenkette gefüllt, anderenfalls mit einem führenden und einem folgenden Leerzeichen versehen. Danach fragt sie in einer Select Case<\/b>-Bedingung mit ctl.ControlType <\/b>zunächst den Typ des Steuerelements mit dem Vergleichswert ab. Liefert dies acTextBox<\/b>, handelte es sich um ein Textfeld. Die Prozedur prüft dann, ob ctl <\/b>überhaupt einen Wert enthält. Falls nicht, geschieht nichts weiter – strWhere<\/b> wird dann nicht geändert.<\/p>\n Enthält ctl <\/b>jedoch eine Zeichenkette mit mindestens einem Zeichen, fragt die Prozedur in einer weiteren If…Then<\/b>-Bedingung ab, ob für den Parameter bolDate <\/b>der Wert True <\/b>übergeben wurde. Dann handelt es sich um ein Datum, was wir in der If<\/b>-Zeile jedoch noch mit IsDate(ctl.Value) <\/b>bestätigen lassen. Liefern die beiden mit And <\/b>verknüpften Ausdrücke den Wert True<\/b>, behandeln wir den Wert von ctl.Value <\/b>als Datum und schreiben den mit ISODatum <\/b>konvertierten Wert in die Variable strValue<\/b>.<\/p>\n Handelt es sich nicht um ein Datum, prüft die Prozedur im ElseIf<\/b>-Zweig, ob es sich bei ctl.Value <\/b>um einen numerischen Wert handelt. Falls ja, wird dieser in strValue <\/b>geschrieben, nachdem die Kommata durch Punkte ersetzt wurden.<\/p>\n Schließlich behandeln wir alle übrigen Werte als Zeichenketten und fügen Sie, in Hochkommata eingeschlossen, in die Variable strValue <\/b>ein. In diesen drei Fällen fügen wir dann den AND\/OR-Operator, den Feldnamen aus strField<\/b>, den Vergleichsoperator aus strOperator <\/b>und den Wert aus strValue <\/b>hinten an strConditions <\/b>an.<\/p>\n Die Prozedur behandelt außerdem noch Kontrollkästchen in einem zweiten Case<\/b>-Zweig der Select Case<\/b>-Bedingung. Hier prüft sie ebenfalls, ob ctl.Value <\/b>den Wert Null <\/b>liefert, was bei nicht initialisierten Kontrollkästchen durchaus passieren kann. In diesem Fall wird keine weitere Bedingung an strCondition <\/b>angehängt. Enthält das Kontrollkästchen einen anderen Wert als Null, wird dieser mit CInt <\/b>in einen Integer<\/b>-Wert umgewandelt und landet mit den übrigen Parameterwerten in einer neuen Bedingung in strCondition<\/b>.<\/p>\n Auf diese Weise können wir das Zusammenstellen der Bedingungs-Teilausdrücke wesentlich einfacher gestalten. Die weiter oben vorgestellten Zeilen sehen in der Prozedur nun wie in Listing 3 aus. Nicht nur, dass wir weniger Schreibarbeit haben, der Code ist auch noch wesentlich besser lesbar, da wir nur noch jeweils eine Zeile pro Bedingung betrachten müssen.<\/p>\n<\/p>\nDim <\/span>strWhere As String<\/span>\r\nIf <\/span>Not<\/span> Len<\/span>(Nz(Me!txtSucheNachFirma, \"\")) = 0 Then<\/span>\r\n strWhere = strWhere & \" AND Firma LIKE '\" & Me!txtSucheNachFirma & \"'\"\r\nEnd If<\/span>\r\nIf <\/span>Not<\/span> Len<\/span>(Nz(Me!txtSucheNachVorname, \"\")) = 0 Then<\/span>\r\n strWhere = strWhere & \" AND Vorname LIKE '\" & Me!txtSucheNachVorname & \"'\"\r\nEnd If<\/span>\r\nIf <\/span>Not<\/span> Len<\/span>(Nz(Me!txtSucheNachNachname, \"\")) = 0 Then<\/span>\r\n strWhere = strWhere & \" AND Nachname LIKE '\" & Me!txtSucheNachNachname & \"'\"\r\nEnd If<\/span>\r\nIf <\/span>Not<\/span> Len<\/span>(Nz(Me!txtSucheNachGeburtsdatum, \"\")) = 0 Then<\/span>\r\n strWhere = strWhere & \" AND Geburtsdatum = \" & ISODatum(Me!txtSucheNachGeburtsdatum)\r\nEnd If<\/span>\r\nIf <\/span>Not<\/span> Len<\/span>(Nz(Me!txtSucheNachKreditscoreVon, \"\")) = 0 Then<\/span>\r\n strWhere = strWhere & \" AND Kreditscore >= \" & Replace<\/span>(Me!txtSucheNachKreditscoreVon, \",\", \".\")\r\nEnd If<\/span>\r\nIf <\/span>Not<\/span> Len<\/span>(Nz(Me!txtSucheNachKreditscoreBis, \"\")) = 0 Then<\/span>\r\n strWhere = strWhere & \" AND Kreditscore <= \" & Replace<\/span>(Me!txtSucheNachKreditscoreBis, \",\", \".\")\r\nEnd If<\/span>\r\nIf <\/span>Not<\/span> IsNull(Me!chkSucheNachNewsletter) Then<\/span>\r\n strWhere = strWhere & \" AND Newsletter = \" & CInt(Me!chkSucheNachNewsletter)\r\nEnd If \r\nIf <\/span>Len<\/span>(strWhere) > 0 Then<\/span>\r\n strWhere = Mid<\/span>(strWhere, 6)\r\n strWhere = \" WHERE \" & strWhere\r\nEnd If<\/span>\r\nDebug.Print<\/span> strWhere<\/pre>\nIf <\/span>Not<\/span> Len<\/span>(Nz(Me!txtSucheNachFirma, \"\")) = 0 Then<\/span>\r\n strWhere = strWhere & \" AND Firma LIKE '\" & Me!txtSucheNachFirma & \"'\"\r\nEnd If<\/span><\/pre>\nIf <\/span>Not<\/span> Len<\/span>(Nz(Me!txtSucheNachGeburtsdatum, \"\")) = 0 Then<\/span>\r\n strWhere = strWhere & \" AND Geburtsdatum = \" & ISODatum(Me!txtSucheNachGeburtsdatum)\r\nEnd If<\/span><\/pre>\nIf <\/span>Not<\/span> Len<\/span>(Nz(Me!txtSucheNachKreditscoreVon, \"\")) = 0 Then<\/span>\r\n strWhere = strWhere & \" AND Kreditscore >= \" & Replace<\/span>(Me!txtSucheNachKreditscoreVon, \",\", \".\")\r\nEnd If<\/span><\/pre>\nIf <\/span>Not<\/span> IsNull(Me!chkSucheNachNewsletter) Then<\/span>\r\n strWhere = strWhere & \" AND Newsletter = \" & CInt(Me!chkSucheNachNewsletter)\r\nEnd If<\/span><\/pre>\nIf <\/span>Len<\/span>(strWhere) > 0 Then<\/span>\r\n strWhere = Mid<\/span>(strWhere, 6)\r\n strWhere = \" WHERE \" & strWhere\r\nEnd If<\/span><\/pre>\nWHERE Firma LIKE 'Firma' AND Vorname LIKE 'Vorname' \r\nAND Nachname LIKE 'Nachname' \r\nAND Geburtsdatum = #2020\/01\/01 00:00:00# \r\nAND Kreditscore >= 90 AND Kreditscore <= 100 \r\nAND Newsletter = -1<\/pre>\n
Vereinfachung 1: Hochkommata<\/h2>\n
Vereinfachung 2: If…Then-Abfragen<\/h2>\n
Vereinfachung 3: Unterscheidung verschiedener Datentypen<\/h2>\n
Prozedur zum Hinzufügen einer Bedingung<\/h2>\n
\n
Public Sub <\/span>AddCondition(strCondition As String<\/span>, strField As String<\/span>, strOperator As String<\/span>, ctl As <\/span>Control, _\r\n strAndOr As String<\/span>, Optional<\/span> bolDate As Boolean<\/span>)\r\n Dim <\/span>strValue As String<\/span>\r\n If <\/span>Len<\/span>(strCondition) = 0 Then<\/span>\r\n strAndOr = \"\"\r\n Else<\/span>\r\n strAndOr = \" \" & strAndOr & \" \"\r\n End If<\/span>\r\n Select Case ctl.ControlType\r\n Case <\/span>acTextBox\r\n If <\/span>Not<\/span> Len<\/span>(Nz(ctl.Value, \"\")) = 0 Then<\/span>\r\n If <\/span>bolDate = True<\/span> And IsDate(ctl.Value) Then<\/span>\r\n strValue = ISODatum(ctl.Value)\r\n Else<\/span>If IsNumeric(ctl.Value) Then\r\n strValue = Replace<\/span>(ctl.Value, \",\", \".\")\r\n Else<\/span>\r\n strValue = \"'\" & ctl.Value & \"'\"\r\n End If<\/span>\r\n strCondition = strCondition & strAndOr & strField & \" \" & strOperator & \" \" & strValue\r\n End If<\/span>\r\n Case <\/span>acCheckBox\r\n If <\/span>Not<\/span> IsNull(ctl.Value) Then<\/span>\r\n strValue = CInt(ctl.Value)\r\n strCondition = strCondition & strAndOr & strField & \" \" & strOperator & \" \" & strValue\r\n End If<\/span>\r\n Case Else<\/span>\r\n End Select<\/span>\r\nEnd Sub<\/span><\/pre>\nAddCondition strWhere, \"Firma\", \"LIKE\", Me!txtSucheNachFirma, \"AND\"<\/pre>\n
Private Sub <\/span>cmdSuchen_Click()\r\n Dim <\/span>strWhere As String<\/span>\r\n AddCondition strWhere, \"Firma\", \"LIKE\", Me!txtSucheNachFirma, \"AND\"\r\n AddCondition strWhere, \"Vorname\", \"LIKE\", Me!txtSucheNachVorname, \"AND\"\r\n AddCondition strWhere, \"Nachname\", \"LIKE\", Me!txtSucheNachNachname, \"AND\"\r\n AddCondition strWhere, \"Geburtsdatum\", \"=\", Me!txtSucheNachGeburtsdatum, \"AND\", True<\/span>\r\n AddCondition strWhere, \"Kreditscore\", \">=\", Me!txtSucheNachKreditscoreVon, \"AND\"\r\n AddCondition strWhere, \"Kreditscore\", \"<=\", Me!txtSucheNachKreditscoreBis, \"AND\"\r\n AddCondition strWhere, \"Newsletter\", \"=\", Me!chkSucheNachNewsletter, \"AND\"\r\n If <\/span>Len<\/span>(strWhere) > 0 Then<\/span>\r\n strWhere = \" WHERE \" & strWhere\r\n End If<\/span>\r\nEnd Sub<\/span><\/pre>\n