Suchkriterien einfach zusammenstellen

Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.

Bild 1: Beispiel-Suchformular

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.

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.

Bild 1: Beispiel-Suchformular

In einer Prozedur, die beispielsweise durch eine Suchen-Schaltfläche ausgelöst wird, stehen dann Anweisungen wie die aus Listing 1.

Dim strWhere As String
If Not Len(Nz(Me!txtSucheNachFirma, "")) = 0 Then
     strWhere = strWhere & " AND Firma LIKE '" & Me!txtSucheNachFirma & "'"
End If
If Not Len(Nz(Me!txtSucheNachVorname, "")) = 0 Then
     strWhere = strWhere & " AND Vorname LIKE '" & Me!txtSucheNachVorname & "'"
End If
If Not Len(Nz(Me!txtSucheNachNachname, "")) = 0 Then
     strWhere = strWhere & " AND Nachname LIKE '" & Me!txtSucheNachNachname & "'"
End If
If Not Len(Nz(Me!txtSucheNachGeburtsdatum, "")) = 0 Then
     strWhere = strWhere & " AND Geburtsdatum = " & ISODatum(Me!txtSucheNachGeburtsdatum)
End If
If Not Len(Nz(Me!txtSucheNachKreditscoreVon, "")) = 0 Then
     strWhere = strWhere & " AND Kreditscore >= " & Replace(Me!txtSucheNachKreditscoreVon, ",", ".")
End If
If Not Len(Nz(Me!txtSucheNachKreditscoreBis, "")) = 0 Then
     strWhere = strWhere & " AND Kreditscore <= " & Replace(Me!txtSucheNachKreditscoreBis, ",", ".")
End If
If Not IsNull(Me!chkSucheNachNewsletter) Then
     strWhere = strWhere & " AND Newsletter = " & CInt(Me!chkSucheNachNewsletter)
End If 
If Len(strWhere) > 0 Then
     strWhere = Mid(strWhere, 6)
     strWhere = " WHERE " & strWhere
End If
Debug.Print strWhere

Listing 1: Zusammensetzen einer Where-Bedingung

Hier deklarieren wir zu Beginn eine Variable namens strWhere, welche den Inhalt der WHERE-Klausel einer SELECT-Abfrage aufnehmen soll. Diese werden dann später zusammengesetzt und beispielsweise einem Unterformular oder einem Listenfeld als Datenquelle zugewiesen (über die Eigenschaft Recordsource oder RowSource).

In unserem Beispiel haben wir einige Felder, die einfache Texte abfragen wie txtSucheNachFirma. Andere fragen nach einem Datum wie txtSucheNachGeburtsdatum oder nach Zahlenwerten (txtSucheNachKreditscoreBis).

Für alle Suchsteuerelemente prüfen wir zunächst, ob dieses überhaupt einen Wert enthält (Len(Nz(, „“))). Die Nz-Funktion wandelt Null-Werte in leere Zeichenfolgen um, die Len-Funktion ermittelt die Zeichenkettenlänge. Auf diese Weise prüfen wir in der If…Then-Bedingung jeweils, ob sich ein Suchbegriff im Steuerelement befindet. Nur dann führen wir den Teil innerhalb der Bedingung aus und fügen einen Teil zur Variablen strWhere hinzu:

If Not Len(Nz(Me!txtSucheNachFirma, "")) = 0 Then
     strWhere = strWhere & " AND Firma LIKE '"  & Me!txtSucheNachFirma & "'"
End If

Im ersten Fall fügen wir direkt einen Ausdruck hinzu, der mit AND beginnt, dann den Namen des zu untersuchenden Feldes (Firma), den Vergleichsoperator (LIKE) und den Vergleichswert (der Inhalt von txtSucheNachFirma in Hochkommata). Frage: Warum beginnen wir gleich den ersten Ausdruck mit AND, obwohl wir diesen Teil später, wenn wir diesen an WHERE anhängen, sowieso entfernen müssen Der Grund ist einfach: Wenn wir allen Elementen der Where-Klausel ein AND voranstellen, können wir auch davon ausgehen, dass bei Vorhandensein mindestens eines Ausdrucks auch ein AND ganz vorn steht – und dieses dann einfach abschneiden.

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, in einen universell einsetzbaren SQL-Datumsausdruck umzuwandeln, in diesem Fall #2020/12/31 00:00:00#. Dazu verwenden wir die Hilfsfunktion ISODatum, die Sie im Modul mdlTools finden:

If Not Len(Nz(Me!txtSucheNachGeburtsdatum, "")) = 0 Then
     strWhere = strWhere & " AND Geburtsdatum = "  & ISODatum(Me!txtSucheNachGeburtsdatum)
End If

Beim Datum geben wir im Gegensatz zum Auch hier stellen wir dem Teilausdruck ein AND voran und fügen diesen dann zum bereits vorhandenen Ausdruck strWhere 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-Prozedur nutzen, um ein eventuell vorhandenes Komma durch einen Punkt als Dezimaltrennzeichen ersetzen:

If Not Len(Nz(Me!txtSucheNachKreditscoreVon, "")) = 0 Then
     strWhere = strWhere & " AND Kreditscore >= "  & Replace(Me!txtSucheNachKreditscoreVon, ",", ".")
End If

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, Nein, Wahr oder Falsch nicht als gültige Werte an. Dementsprechend liefert ein Ausdruck wie Newsletter = Ja 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:

If Not IsNull(Me!chkSucheNachNewsletter) Then
     strWhere = strWhere & " AND Newsletter = "  & CInt(Me!chkSucheNachNewsletter)
End If

Schließlich prüfen wir am Ende, ob strWhere überhaupt eine Länge größer als 0 hat. Falls ja, wissen wir auch, dass die ersten fünf Buchstaben ein Leerzeichen, die Zeichenkette AND und noch ein Leerzeichen sind. Damit können wir dann einfach mit Mid(strWhere, 6) nur den Inhalt ab dem sechsten Zeichen weiterverwenden und diesem noch das WHERE-Schlüsselwort voranstellen:

If Len(strWhere) > 0 Then
     strWhere = Mid(strWhere, 6)
     strWhere = " WHERE " & strWhere
End If

Dies ergibt dann beispielsweise einen Ausdruck wie den folgenden:

WHERE Firma LIKE 'Firma' AND Vorname LIKE 'Vorname' 
AND Nachname LIKE 'Nachname' 
AND Geburtsdatum = #2020/01/01 00:00:00# 
AND Kreditscore >= 90 AND Kreditscore <= 100 
AND Newsletter = -1

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. Nun wollen wir uns um den Rest kümmern und die Erstellung des Where-Ausdrucks wesentlich vereinfachen.

Vereinfachung 1: Hochkommata

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

Testzugang

eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel

diesen und alle anderen Artikel mit dem Jahresabo