Gemeinsam zu mehr Effizienz in der Anwendungserstellung
K |
K (Verweis auf Artikel in Access im Unternehmen) |
||
(Der Versionsvergleich bezieht 69 dazwischenliegende Versionen mit ein.) | |||
Zeile 1: | Zeile 1: | ||
[[Kategorie:Code-Schnipsel]] | [[Kategorie:Code-Schnipsel]] | ||
+ | Wenn ein Filterausdruck als String benötigt wird, müssen die Filterwerte SQL-konform (abhängig vom Datentyp) in Text konvertiert werden.<br /> | ||
+ | Der zu erzeugende Text hängt vom eingesetzten DBMS und/oder der verwendeten Datenzugriffstechnik ab. | ||
- | + | ==SQL-Text in Abhängigkeit des Datentyps== | |
+ | ===Text=== | ||
+ | Der Text-Filterwert muss mit {{Taste|'}} umschlossen sein. Bei Jet/ACE ist auch {{Taste|"}} möglich. | ||
- | + | SQL-Text für Filterwert: abc | |
- | + | ||
- | + | ||
- | Filterwert: abc | + | |
<source> | <source> | ||
... Textfeld = 'abc' | ... Textfeld = 'abc' | ||
</source> | </source> | ||
- | Dabei ist zu beachten, dass ' im Filterwert | + | Dabei ist zu beachten, dass bei der Verwendung von {{Taste|'}} als Textbegrenzung im Filterwert vorhandene {{Taste|'}} verdoppelt werden müssen, da sonst der Text zu früh als beendet gekennzeichnet wird. |
- | Filterwert: a'b | + | |
+ | SQL-Text für Filterwert: a'b | ||
<source> | <source> | ||
... Textfeld = 'a''c' | ... Textfeld = 'a''c' | ||
</source> | </source> | ||
- | + | VBA-Code mit in Variable gespeichertem Filterwert: | |
- | + | ||
<source> | <source> | ||
FilterString = "Textfeld = '" & Replace(FilterWertVariable, "'", "''") & "'" | FilterString = "Textfeld = '" & Replace(FilterWertVariable, "'", "''") & "'" | ||
</source> | </source> | ||
+ | |||
+ | {{remark|text=Die gleichen Regeln gelten natürlich auch bei Bedingungen mit Like, >=, <= usw.}} | ||
+ | |||
+ | ===Zahlen (Integer, Double, ...)=== | ||
+ | Zahlen sind im amerikanischen Zahlenformat darzustellen (Punkt statt Komma). | ||
+ | |||
+ | SQL-Text für Filterwert: 123,45 | ||
+ | <source> | ||
+ | ... Textfeld = 123.45 | ||
+ | </source> | ||
+ | |||
+ | VBA-Code mit in Variable gespeichertem Filterwert: | ||
+ | <source> | ||
+ | FilterString = "Zahlenfeld = " & Str(FilterWertVariable) | ||
+ | </source> | ||
+ | |||
+ | ===Datum/Uhrzeit=== | ||
+ | Das Datumsformat hängt vom eingesetzten DBMS ab. | ||
+ | |||
+ | ; Beispiele: | ||
+ | : Jet: <code>#mm/dd/yyyy#</code> oder <code>#yyyy-mm-dd#</code> | ||
+ | : MSSQL: <code>'yyyymmdd'</code> | ||
+ | |||
+ | SQL-Text für Filterwert: 24.12.2015 | ||
+ | <source> | ||
+ | ... Datumsfeld = #2015-12-24# | ||
+ | </source> | ||
+ | |||
+ | VBA-Code mit in Variable gespeichertem Filterwert: | ||
+ | <source> | ||
+ | FilterString = "Datumsfeld = " & Format(FilterWertVariable, "\#yyyy-mm-dd\#") | ||
+ | </source> | ||
+ | |||
+ | Anm.: | ||
+ | Auch schon gesehen: | ||
+ | <source> | ||
+ | FilterString = "Datumsfeld = " & CLng(FilterWertVariable) | ||
+ | </source> | ||
+ | Davon ist abzuraten, da das z. B. nicht mit ODBC-verknüpften MSSQL-Tabellen funktioniert - wegen unterschiedlichem Datumsstartwert (Zahl 0) zw. VBA und MSSQL. | ||
+ | |||
+ | ===Ja/Nein, Wahr/Falsch=== | ||
+ | Das Boolean-Format hängt vom eingesetzten DBMS ab. | ||
+ | ; Beispiele: | ||
+ | : Jet: <code>True</code> oder <code>-1</code> | ||
+ | : MSSQL: <code>1</code> | ||
+ | |||
+ | SQL-Text für Filterwert: True | ||
+ | <source> | ||
+ | ... Booleanfeld = True | ||
+ | </source> | ||
+ | |||
+ | {{info|text=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. | ||
+ | <br />''Gemeinheit'' dabei: in der verknüpften MSSQL-Tabelle wird True als -1 angezeigt und auch -1 im Select-Teil einer DAO-Abfrage angeboten. 1 als True gilt nur für Filterbedingungen, die an den Server übergeben werden. Sobald Access die Filterung ohne ODBC übernimmt, könnte -1 verwendet werden.}} | ||
+ | |||
+ | VBA-Code mit in Variable gespeichertem Filterwert: | ||
+ | <source> | ||
+ | FilterString = "Booleanfeld= " & IIF(FilterWertVariable = True, "True", "False") | ||
+ | </source> | ||
+ | |||
+ | ==Mehrere Kriterien verbinden== | ||
+ | Werden mehrere Filterbedingungen in einem SQL-String benötigt, sind diese je nach Bedarf mit ''<code>And</code>'' oder ''<code>Or</code>'' zu verbinden. | ||
+ | In VBA erfolgt dies mittels Zusammensetzen von mehreren String-Werten. | ||
+ | |||
+ | Prinzip: | ||
+ | <source>FilterAusdruck = FilterStringVariable1 & " And " & FilterStringVariable2</source> | ||
+ | Voraussetzung für obigen Ausdruck: alle beiden Filterstring-Variablen müssen einen Filterausdruck enthalten. | ||
+ | |||
+ | '''Beispiele mit dynamisch zusammengesetztem Filterausdruck in einem Formular''' | ||
+ | |||
+ | a) String zusammensetzen | ||
+ | <source> | ||
+ | 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; "<" | ||
+ | </source> | ||
+ | |||
+ | b) Filter in Array speichern und mit Join den Filterstring erzeugen | ||
+ | <source> | ||
+ | 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; "<" | ||
+ | </source> | ||
+ | |||
+ | '''Weiterführende Informationen''' | ||
+ | {{info|text=Eine ausführlichere Beschreibung der Vorgehensweise gibt es im Beitrag ''Filterbedingungen einfach zusammenstellen'' in der Ausgabe 6/2015 von [http://www.access-im-unternehmen.de/ Access im Unternehmen] auf Seite 44 ([http://www.access-im-unternehmen.de/fileadmin/download/AIU201506_Leseprobe.pdf Leseprobe des Heftes 6/2015])}} | ||
+ | |||
+ | ==Einsatz von Hilfsprozeduren und Klassen aus der CodeLib== | ||
+ | '''Hilfsfunktion zur Umwandlung in Text aus {{websvn|path=data/SqlTools.cls|text=Sqltools}}''' | ||
+ | <source> | ||
+ | FilterString = "Textfeld = " & SqlTools.TextToSqlText(FilterWertVariable) | ||
+ | </source> | ||
+ | <source> | ||
+ | FilterString = "Zahlenfeld = " & SqlTools.NumberToSqlText(FilterWertVariable) | ||
+ | </source> | ||
+ | <source> | ||
+ | FilterString = "Datumsfeld = " & SqlTools.DateToSqlText(FilterWertVariable) | ||
+ | </source> | ||
+ | <source> | ||
+ | FilterString = "Booleanfeld = " & SqlTools.BooleanToSqlText(FilterWertVariable) | ||
+ | </source> | ||
+ | |||
+ | '''''BuildCriteria'' aus {{websvn|path=data/SqlTools.cls|text=Sqltools}}''' | ||
+ | <source> | ||
+ | FilterString = SqlTools.BuildCriteria("Textfeld", SQL_Text, SQL_Equal, FilterWertVariable) | ||
+ | </source> | ||
+ | <source> | ||
+ | FilterString = SqlTools.BuildCriteria("Zahlenfeld", SQL_Numeric, SQL_Equal, FilterWertVariable) | ||
+ | </source> | ||
+ | <source> | ||
+ | FilterString = SqlTools.BuildCriteria("Datumsfeld", SQL_DateTime, SQL_Equal, FilterWertVariable) | ||
+ | </source> | ||
+ | <source> | ||
+ | FilterString = SqlTools.BuildCriteria("Booleanfeld", SQL_Boolean, SQL_Equal, FilterWertVariable) | ||
+ | </source> | ||
+ | |||
+ | '''Klasse {{websvn|path=data/FilterStringBuilder.cls|text=FilterStringBuilder}}''' | ||
+ | <source> | ||
+ | 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 | ||
+ | </source> | ||
+ | |||
+ | {{related links|links= | ||
+ | [[ACLib-FilterForm-Wizard]] | ||
+ | }} |
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
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 und auch -1 im Select-Teil einer DAO-Abfrage angeboten. 1 als True gilt nur für Filterbedingungen, die an den Server übergeben werden. Sobald Access die Filterung ohne ODBC übernimmt, könnte -1 verwendet werden.
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 " & FilterStringVariable2
Voraussetzung 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; "<"
Weiterführende Informationen
Eine ausführlichere Beschreibung der Vorgehensweise gibt es im Beitrag Filterbedingungen einfach zusammenstellen in der Ausgabe 6/2015 von Access im Unternehmen auf Seite 44 (Leseprobe des Heftes 6/2015)
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
Weiterführende Links: ACLib-FilterForm-Wizard