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