Filterbedingungen einfach zusammenstellen

2015, 6/2015, VBA und Programmiertechniken

Erstellen Sie in Ihren Anwendungen mit VBA Filterausdrücke, um Formulare zu filtern oder Recordsets mit einer gefilterten Datenherkunft zu öffnen Sind Ihnen dabei schon Fehler passiert, weil Sie die Werte nicht richtig konvertiert haben Oder finden Sie den Code zum Filtern zu aufwendig und suchen eine Möglichkeit, die Erstellung des Filtertextes zu vereinfachen Im vorliegenden Beitrag werden Sie verfolgen, wie eine Code-Struktur entsteht, die alle notwendigen Konvertierungsregeln berücksichtigt und trotzdem in der Anwendung übersichtlich ist und Programmierfehler vermeidet.

Problemstellung

In Access-Anwendungen wird relativ oft mittels VBA ein Filterausdruck zusammengestellt. Typische Beispiele sind:

  • SQL-Anweisung zum öffnen eines Recordset (Currentdb.OpenRecordset und ähnliche)
  • WHERE-Parameter von DoCmd.OpenForm oder DoCmd.OpenReport
  • Setzen der Filter-Eigenschaft in Formularen

Beim Zusammensetzen der SQL-Texte werden – wie Sie in vielen Beiträgen in Foren lesen können – regelmäßig Fehler gemacht. Es wird der Datentyp nicht SQL-konform in Text konvertiert, mögliche Null-Werte werden übersehen, statt dem Variableninhalt wird der Variablenname in den SQL-Ausdruck übernommen und so weiter. Damit Ihnen diese Fehler nicht passieren, beschreiben wir zuerst die Grundlagen für das Zusammenstellen von SQL-Anweisungen in VBA. Anschließend erstellen wir einen Code (in Form von Klassen), der die richtige Syntax für Sie erzeugt. Sie müssen den Klassen in Ihren Anwendungen nur noch Ihr Vorhaben mittels VBA-Anweisung mitteilen, um den gewünschten Filterausdruck zu erstellen.

Syntax in Abhängigkeit vom Datentyp

Bei der Konvertierung von Filterwerten in einen SQL-Text müssen Sie jeden Datentyp passend für das verwendete Datenbanksystem konvertieren. Die folgenden Absätze beschreiben dies für die relevanten Datentypen.

Text

Ein Text muss mit Hochkommata (“) umschlossen werden. Je nach Datenbanksystem sind auch Anführungszeichen („) oder ähnliches möglich. Im weiteren Text werden wir zur Vereinfachung von Access/Jet-SQL für DAO ausgehen.

Beispiele:

... WHERE Textfeld = ''Filtertext''
... WHERE Textfeld LIKE ''Filtertext*''

Wenn im zu filternden Text ein Hochkomma (“) enthalten ist (Beispiel: O“Neill), muss das Zeichen verdoppelt werden, damit der SQL-Interpreter erkennen kann, dass der Text noch nicht zu Ende ist. Aus dem Ausdruck

... WHERE Textfeld = ''O''Neill''

wird dann der folgende Ausdruck (mit O““Neill):

... WHERE Textfeld = ''O''''Neill''

Zahlen

Zahlen sind in englischer Schreibweise mit Punkt als Dezimaltrennzeichen zu verwenden.

... WHERE Zahlfeld = 1.23

Datum

Datumswerte sind im SQL-Text für Jet-SQL mit dem Raute-Zeichen (#) zu umschließen. Die Reihenfolge für Tag, Monat und Jahr muss in amerikanischer Schreibweise (mm/dd/yyyy) oder im ISO-Format (yyyy-mm-dd) erfolgen. Die erforderlichen Datumsformate sehen je nach Datenbanksystem unterschiedlich aus. Beispiele für Jet-SQL:

... WHERE Datumsfeld = #12/24/2015#
... WHERE Datumsfeld = #2015-12-24#
... WHERE Datumsfeld = #2015-12-24 16:30:00#

T-SQL (SQL-Server):

... WHERE Datumsfeld = ''20151224''
... WHERE Datumsfeld = ''20151224 16:30:00''

Ja/Nein (Boolean)

In Access-Datenbanken wird True als -1 in Zahlenschreibweise verwendet. Der SQL-Server (T-SQL) betrachtet 1 als True bei Bit-Datenfeldern. Im SQL-Text einer Access-Abfrage beziehungsweise DAO-Recordset und ähnlichen ist die Angabe von True statt -1 zu empfehlen.

Dann gibt es auch bei Abfragen auf über ODBC verknüpfte SQL-Server-Tabellen keine Probleme.

... WHERE JaNeinFeld = True

Stolperfallen

Beim Zusammensetzen eines SQL-Textes entstehen die meisten Fehler beim Einbinden von Werten aus Variablen oder Steuerelementen in den SQL-Text.

  • Die Konvertierung der Variablenwerte in einen SQL-Ausdruck wird nicht beziehungsweise falsch durchgeführt.
  • Verdoppeln des Begrenzungszeichens vom Text (meist “) wird übersehen, falls das Zeichen im Text enthalten ist.
  • Wenn die Zusammenstellung des SQL-Textes über mehrere VBA-Zeilen erfolgt, werden notwendige Leerzeichen übersehen.

Diese Stolperfallen können Sie problemlos vermeiden, wenn Sie die Vorgehensweise für das Zusammenstellen eines SQL-Textes verinnerlichen und die für Ihr Datenbanksystem gültige SQL-Syntax kennen.

Vom statischen Text zum dynamisch aus Werten in Steuerelementen erstellten Text

Wenn Sie nicht sicher sind, wie ein SQL-Text mit Filterwerten aus Steuerelementen oder Variablen zusammengesetzt wird, versuchen Sie die Erstellung in umgekehrter Reihenfolge anzugehen.

Nehmen Sie zum Ausprobieren der SQL-Anweisungen einen fixen Filterwert (welcher später etwa durch eine Benutzereingabe übergeben wird) an und erstellen im Access-Abfrageeditor eine SQL-Anweisung. Die Funktionsfähigkeit dieser SQL-Anweisung testen Sie nun. Wenn die Abfrage das erwartete Ergebnis liefert, wissen Sie, dass diese SQL-Anweisung passt.

Beispiel:

SELECT * FROM tabTest WHERE Textfeld LIKE ''abc*''

Stufe 1: Vollständiger SQL-Text in Variable

Nun kopieren Sie diese SQL-Anweisung und speichern sie in einer String-Variable in einer Test-Prozedur, wie in Listing 1 gezeigt, ab.

Private Sub SqlText_VBA_Stufe1()
    Dim SqlText As String
    SqlText = "SELECT * FROM tabTest WHERE Textfeld LIKE ''abc*''"
    Call SqlAnweisungAuswerten(SqlText)
End Sub

Listing 1: VBA-Ausbaustufe 1

Zum öffnen eines Recordsets mit dem in VBA gespeicherten SQL-Text und zur Ausgabe der enthaltenen Datensätze in den Direktbereich verwenden wir die Hilfsprozedur aus Listing 2.

Private Sub SqlAnweisungAuswerten(ByVal SqlText As String)
    Debug.Print "SQL: "; SqlText
    With CurrentDb.OpenRecordset(SlText)
       Do While Not .EOF
          Debug.Print "ID: "; .Fields("idTest"), _
                      "Textfeld: "; .Fields("Textfeld")
          .MoveNext
       Loop
       .Close
    End With
End Sub

Listing 2: Hilfsprozedur zum Auswerten einer SQL-Anweisung

Stufe 2: Filterausdruck als extra String verketten

Im nächsten Ausbauschritt (s. Listing 3) trennen wir den Filterausdruck von der restlichen SQL-Anweisung (SqlText_VBA_Stufe2a). Desweiteren speichern wir den Filterausdruck in einer zusätzlichen Variablen ab und verketten diese Variable mit dem restlichen SQL-Ausdruck (SqlText_VBA_Stufe2b). Zusätzlich zur Variable für den gesamten Filterausdruck können wir den Filterwert (im SQL-Format) in einer Stringvariable speichern (SqlText_VBA_Stufe2c).

Private Sub SqlText_VBA_Stufe2a()
     Dim SqlText As String
     SqlText = "SELECT * FROM tabTest WHERE " & "Textfeld LIKE ''abc*''"
     Call SqlAnweisungAuswerten(SqlText)
End Sub
Private Sub SqlText_VBA_Stufe2b()
    Dim SqlText As String
    Dim SqlFilter As String
    SqlFilter = "Textfeld LIKE ''abc*''"
    SqlText = "SELECT * FROM tabTest WHERE " & SqlFilter
    Call SqlAnweisungAuswerten(SqlText)
End Sub
Private Sub SqlText_VBA_Stufe2c()
    Dim SqlText As String
    Dim SqlFilter As String
    Dim WertImSqlFormat As String
    WertImSqlFormat = "''abc*''"
    SqlFilter = "Textfeld LIKE " & FilterWertImSqlFormat
    SqlText = "SELECT * FROM tabTest WHERE " & SqlFilter
    Call SqlAnweisungAuswerten(SqlText)
End Sub

Listing 3: VBA-Ausbaustufe 2

Stufe 3: Filterwerte in passenden SQL-Text konvertieren

In den obigen Beispielen wurde der SQL-Text für den Filterwert im passenden SQL-Format in der Variable gespeichert.

üblicherweise muss der Filterwert in SQL-Text konvertiert werden, weil die Werte zum Beispiel aus Steuerelementen in Formularen oder aus VBA-Variablen kommen, die nur den Wert und nicht dessen SQL-Ausdruck enthalten.

Ablauf des Codes in Listing 4: Aus dem String abc* soll der SQL-Ausdruck “abc*“ entstehen. Dazu werden die Hochkommata (“) mit dem Filterwert verkettet („““ & „abc*“ & „““). Von den anfangs genannten Regeln wissen wir, dass die im Filterwert enthaltenen Hochkommata verdoppelt werden müssen. Das erledigt die Replace-Funktion.

Private Sub SqlText_VBA_Stufe3()
    Dim SqlText As String
    Dim SqlFilter As String
    Dim WertImSqlFormat As String
    Dim FilterWert As String
    FilterWert = "abc*"
    WertImSqlFormat = "''" & Replace(FilterWert, "''", "''''") & "''"
    SqlFilter = "Textfeld LIKE " & WertImSqlFormat
    SqlText = "SELECT * FROM tabTest WHERE " & SqlFilter
    Call SqlAnweisungAuswerten(SqlText)
End Sub

Listing 4: VBA-Ausbaustufe 3

Bei anderen Datentypen können Sie die gleiche Vorgehensweise anwenden.

Sie müssen nur die Zeile WertimSqlFormat = … entsprechend anpassen, damit der Filterwert in das passende SQL-Format konvertiert wird.

Möglicher Umwandlungscode für Text:

WertImSqlFormat = "''" & Replace(FilterWert, "''", "''''") & "''"

Für Zahlen:

WertImSqlFormat = Str(FilterWert)

Die Str-Funktion nicht mit der CStr-Funktion verwechseln!

Für Datumsangaben:

WertImSqlFormat = Format(FilterWert, "\#yyyy-mm-dd\#")

Für Ja/Nein-Felder (Boolean):

WertImSqlFormat = IIf(FilterWert = True, "True", "False")

Stufe 4: Filter kombinieren

Im nächsten Code-Beispiel (s. Listing 5) erstellen wir SQL-Filtertexte für verschiedene Datentypen und fügen sie zu einem kombinierten Filterausdruck zusammen.

Private Sub SqlText_VBA_Stufe4()
     Dim SqlText As String    
     Dim SqlFilter As String
     Dim SqlFilterAusdruck As String
     Dim WertImSqlFormat As String
     Dim TextWert As Variant
     Dim DoubleWert As Double
     Dim DatumsWert As Date
     Dim BooleanWert As Boolean
     ''Text
     TextWert = "abc*"
     WertImSqlFormat = "''" & Replace(TextWert, "''", "''''") & "''"
     SqlFilterAusdruck = "Textfeld LIKE " & WertImSqlFormat
     SqlFilter = SqlFilterAusdruck
     ''Zahlen
     DoubleWert = 1.23
     WertImSqlFormat = Trim(Str(DoubleWert))
     SqlFilterAusdruck = "DoubleZahlenfeld > " & WertImSqlFormat
     SqlFilter = SqlFilter & " And " & SqlFilterAusdruck
     ''Datum
     DatumsWert = Date
     WertImSqlFormat = Format(DatumsWert, "\#yyyy-mm-dd\#")
     SqlFilterAusdruck = "Datumsfeld <= " & WertImSqlFormat
     SqlFilter = SqlFilter & " And " & SqlFilterAusdruck
     ''Ja/Nein (Boolean)
     BooleanWert = True
     WertImSqlFormat = IIf(BooleanWert = True, "True", "False")
     SqlFilterAusdruck = "JaNeinFeld = " & WertImSqlFormat
     SqlFilter = SqlFilter & " And " & SqlFilterAusdruck
     ''gesamten SQL-Text zusammenstellen
     SqlText = "SELECT * FROM tabTest WHERE " & SqlFilter
     Call SqlAnweisungAuswerten(SqlText)
End Sub

Listing 5: VBA-Ausbaustufe 4

Stufe 5: Filterwerte aus Formular-Steuerelementen

Häufig werden Formulare zum Filtern von Datenmengen verwendet. In diesen Formularen befinden sich Steuerelemente, in die der Anwender Werte eingeben kann. Wenn in einem Steuerelement ein Wert enthalten ist, sollen die Datensätze entsprechend gefiltert werden. Ist im Steuerelement kein Wert enthalten, soll dieser Filter ignoriert werden.

Typisches Code-Gerüst im Formular-Modul mit den Filtersteuerelementen:

With Me.Steuerelement
     If Not IsNull(.Value) then
         '' Filterausdruck erstellen
     End If
End With

Im Code in Listing 6 werden die Werte aus den Formular-Steuerelementen auf vorhandene Filterwerte geprüft, aus den vorhandenen Einträgen Filterausdrücke erstellt und zu einem kombinierten Filterstring zusammengesetzt.

Private Sub SqlText_VBA_Stufe5()
    Dim SqlText As String
    Dim SqlFilter As String
    Dim SqlFilterAusdruck As String
    Dim WertImSqlFormat As String
    ''Text
    With Me.txtTextFilter
       If Len(.Value) > 0 Then '' Null und Leerstring ignorieren
          WertImSqlFormat = "''" & Replace(.Value, "''", "''''") & "''"
          SqlFilterAusdruck = "Textfeld LIKE " & WertImSqlFormat
          SqlFilter = SqlFilter & " And " & SqlFilterAusdruck
       End If
    End With
    ''Zahlen
    With Me.txtZahlenFilter
       If Not IsNull(.Value) Then
          WertImSqlFormat = Trim(Str(.Value))
          SqlFilterAusdruck = "DoubleZahlenfeld > " & WertImSqlFormat
          SqlFilter = SqlFilter & " And " & SqlFilterAusdruck
       End If
    End With
    ''Datum
    With Me.txtDatumsFilter
       If Not IsNull(.Value) Then
          WertImSqlFormat = Format(.Value, "\#yyyy-mm-dd\#")
          SqlFilterAusdruck = "Datumsfeld <= " & WertImSqlFormat
          SqlFilter = SqlFilter & " And " & SqlFilterAusdruck
       End If
    End With
    ''Filterausdruck aufbereiten
    If Len(SqlFilter) > 0 Then
       '' ersten " And "-Ausdruck wegschneiden
       SqlFilter = Mid(SqlFilter, Len(" And ") + 1)
    End If
    ''gesamten SQL-Text zusammenstellen
    SqlText = "SELECT * FROM tabTest"
    If Len(SqlFilter) > 0 Then
       SqlText = SqlText & " WHERE " & SqlFilter
    End If
    Call SqlAnweisungAuswerten(SqlText)
End Sub

Listing 6: VBA-Ausbaustufe 5

Nach diesem Prinzip können Sie Ihre Filterbedingungen zusammenstellen. Einen Nachteil hat diese Vorgehensweise: Der Code wird bei mehreren Filter-Feldern schnell unübersichtlich.

Ein Refactoring dieses Codes führt uns allerdings zu unserem Vorhaben, eine universell einsetzbare Code-Struktur zu gestalten, die statt dem Programmieren des Filtertextes ein Definieren der Filterbedingungen erlaubt und uns den fertigen SQL-Filtertext liefert.

Zuvor sehen wir uns noch ein bereits in Access eingebautes Hilfsmittel zum Erzeugen eines Filterausdrucks an.

Hilfsfunktion Access.BuildCriteria

Die Methode BuildCriteria vom Access-Objekt erlaubt eine übergabe der Filterwerte als String. Dabei müssen keine SQL-relevanten Zeichen zum Eingrenzen eines Textes oder Datums ergänzt werden. BuildCriteria erzeugt die Zeichen in Abhängigkeit vom FieldType-Parameter selbst.

Syntax: BuildCriteria(Field, FieldType, Expression)

Beispiele finden Sie in Listing 7.

Private Sub BuildCriteriaBeispiele()
    Dim SqlAnweisung As String
    Dim SqlFilter As String
    ''Text
    SqlFilter = BuildCriteria("Textfeld", dbText, "LIKE abc*")
    SqlAnweisung = "SELECT * FROM tabTest WHERE " & SqlFilter
    Call SqlAnweisungAuswerten(SqlAnweisung)
    ''Zahlen
    SqlFilter = BuildCriteria("DoubleZahlenfeld", dbDouble, "> 1,23")
    SqlAnweisung = "SELECT * FROM tabTest WHERE " & SqlFilter
    Call SqlAnweisungAuswerten(SqlAnweisung)
    ''Datum
    SqlFilter = BuildCriteria("Datumsfeld", dbDate, "<=" & Date)
    SqlAnweisung = "SELECT * FROM tabTest WHERE " & SqlFilter
    Call SqlAnweisungAuswerten(SqlAnweisung)
    ''Ja/Nein (Boolean)
    SqlFilter = Access.BuildCriteria("JaNeinFeld", dbBoolean, True)
    SqlAnweisung = "SELECT * FROM tabTest WHERE " & SqlFilter
    Call SqlAnweisungAuswerten(SqlAnweisung)
End Sub

Listing 7: Einsatz von Access.BuildCriteria

Ein Nachteil dieser Hilfsfunktion ist, dass die Funktion nur einen Filterausdruck für Jet/DAO-SQL erzeugt. Falls Sie einen SQL-Text für den SQL-Server in T-SQL für eine Pass-Through-Abfrage benötigen, können Sie diese BuildCriteria-Funktion nicht verwenden. Für unsere Klassenbibliothek können wir uns das Konzept dieser Funktion merken, um etwas ähnliches zu erstellen.

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

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo

Schreibe einen Kommentar