Filterbedingungen einfach zusammenstellen

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