Gemeinsam zu mehr Effizienz in der Anwendungserstellung
Wenn ein Filterausdruck als String benötigt wird, müssen die Filterwerte SQL-konform (abhängig vom Datentyp) in Text konvertiert werden.
Der zu erzeugende Text hängt vom eingesetzten DBMS und/oder der verwendeten Datenzugriffstechnik ab.
Inhaltsverzeichnis |
Der Text-Filterwert muss mit ' umschlossen sein. Bei Jet/ACE ist auch " möglich.
SQL-Text für Filterwert: abc
... Textfeld = 'abc'
Dabei ist zu beachten, dass bei der Verwendung von ' als Textbegrenzung im Filterwert vorhandene ' verdoppelt werden müssen, da sonst der Text zu früh als beendet gekennzeichnet wird.
SQL-Text für Filterwert: a'b
... Textfeld = 'a''c'
VBA-Code mit in Variable gespeichertem Filterwert:
FilterString = "Textfeld = '" & Replace(FilterWertVariable, "'", "''") & "'"
| Anmerkung: Die gleichen Regeln gelten natürlich auch bei Bedingungen mit Like, >=, <= usw. |
Zahlen sind im amerikanischen Zahlenformat darzustellen (Punkt statt Komma).
SQL-Text für Filterwert: 123,45
... Textfeld = 123.45
VBA-Code mit in Variable gespeichertem Filterwert:
FilterString = "Zahlenfeld = " & Str(FilterWertVariable)Das Datumsformat hängt vom eingesetzten DBMS ab.
#mm/dd/yyyy# oder #yyyy-mm-dd#
'yyyymmdd'
SQL-Text für Filterwert: 24.12.2015
... Datumsfeld = #2015-12-24#
VBA-Code mit in Variable gespeichertem Filterwert:
FilterString = "Datumsfeld = " & Format(FilterWertVariable, "\#yyyy-mm-dd#")
Anm.: Auch schon gesehen:
FilterString = "Datumsfeld = " & CLng(FilterWertVariable)
Davon ist abzuraten, da das z. B. nicht mit ODBC-verknüpften MSSQL-Tabellen funktioniert - wegen unterschiedlichem Datumsstartwert (Zahl 0) zw. VBA und MSSQL.
Das Boolean-Format hängt vom eingesetzten DBMS ab.
True oder -1
1
SQL-Text für Filterwert: True
... Booleanfeld = True| Anmerkung: Falls ODBC-verknüpfte MSSQL-Tabellen mit Bit-Feldern verwendet werden, ist von -1 als True-Wert abzuraten.
ODBC konvertiert True richtig als 1 nach MSSQL. Wird jedoch -1 verwendet, gibt der ODBC-Treiber diese Zahl direkt an das DBMS weiter und da -1 nicht 1 entspricht trifft die Bedingung nie zu. Gemeinheit dabei: in der verknüpften MSSQL-Tabelle wird True als -1 angezeigt. |
VBA-Code mit in Variable gespeichertem Filterwert:
FilterString = "Booleanfeld= " & IIF(FilterWertVariable = True, "True", "False")
Werden mehrere Filterbedingungen in einem SQL-String benötigt, sind diese je nach Bedarf mit And oder Or zu verbinden.
In VBA erfolgt dies mittels Zusammensetzen von mehreren String-Werten.
Prinzip:
FilterAusdruck = FilterStringVariable1 & " And " & FilterStringVariable2Voraussetzung für obigen Ausdruck: alle beiden Filterstring-Variablen müssen einen Filterausdruck enthalten.
Beispiele mit dynamisch zusammengesetztem Filterausdruck in einem Formular
a) String zusammensetzen
Dim FilterString As String With Me.FilterTextBox1 If Len(.Value) > 0 Then ' Filterausdruck nur erzeugen, wenn ein Filterwert in die TextBox eingegeben wurde FilterString = FilterString & " And " & "Text1 like '" & Replace(.Value, "'", "''") & "'" End If End With With Me.FilterTextBox2 If Len(.Value) > 0 Then ' Filterausdruck nur erzeugen, wenn ein Filterwert in die TextBox eingegeben wurde FilterString = FilterString & " And " & "Text2 like '" & Replace(.Value, "'", "''") & "'" End If End With If Len(FilterString) > 0 then ' " And " am Anfang wegschneiden FilterString = Mid(FilterString, Len(" And ") + 1) End If Debug.Print "Filterausdruck: >"; FilterString; "<"
b) Filter in Array speichern und mit Join den Filterstring erzeugen
Const MaxAnzahlFilterBedingungen As Long = 5 Dim FilterArray() As String ReDim FilterArray(1 To MaxAnzahlFilterBedingungen) Dim VerwendeteFilter As Long Dim FilterString As String With Me.FilterTextBox1 If Len(.Value) > 0 Then ' Filterausdruck nur erzeugen, wenn ein Filterwert in die TextBox eingegeben wurde VerwendeteFilter = VerwendeteFilter + 1 FilterArray(VerwendeteFilter) = "Text1 like '" & Replace(.Value, "'", "''") & "'" End If End With With Me.FilterTextBox2 If Len(.Value) > 0 Then ' Filterausdruck nur erzeugen, wenn ein Filterwert in die TextBox eingegeben wurde VerwendeteFilter = VerwendeteFilter + 1 FilterArray(VerwendeteFilter) = "Text2 like '" & Replace(.Value, "'", "''") & "'" End If End With If VerwendeteFilter > 0 Then ' " And " am Anfang wegschneiden ReDim Preserve FilterArray(1 To VerwendeteFilter) ' nicht verwendete "Filterplätze abschneiden" FilterString = Join(FilterArray, " And ") End If Debug.Print "Filterausdruck: >"; FilterString; "<"
Hilfsfunktion zur Umwandlung in Text aus Sqltools
FilterString = "Textfeld = " & SqlTools.TextToSqlText(FilterWertVariable)FilterString = "Zahlenfeld = " & SqlTools.NumberToSqlText(FilterWertVariable)FilterString = "Datumsfeld = " & SqlTools.DateToSqlText(FilterWertVariable)FilterString = "Booleanfeld = " & SqlTools.BooleanToSqlText(FilterWertVariable)BuildCriteria aus Sqltools
FilterString = SqlTools.BuildCriteria("Textfeld", SQL_Text, SQL_Equal, FilterWertVariable)FilterString = SqlTools.BuildCriteria("Zahlenfeld", SQL_Numeric, SQL_Equal, FilterWertVariable)FilterString = SqlTools.BuildCriteria("Datumsfeld", SQL_DateTime, SQL_Equal, FilterWertVariable)FilterString = SqlTools.BuildCriteria("Booleanfeld", SQL_Boolean, SQL_Equal, FilterWertVariable)Klasse FilterStringBuilder
With NewFilterStringBuilder .Add "Textfeld", SQL_Text, SQL_Equal, FilterWertVariable1 .Add "Zahlenfeld", SQL_Numeric, SQL_Equal, FilterWertVariable2 .Add "Datumsfeld", SQL_DateTime, SQL_Equal, FilterWertVariable3 .Add "Booleanfeld", SQL_Boolean, SQL_Equal, FilterWertVariable4 FilterString = .ToString End With