Wiki der Access Code Library

Gemeinsam zu mehr Effizienz in der Anwendungserstellung

SQL-Text für Filterbedingung

Aus Access Code Library
(Unterschied zwischen Versionen)
Wechseln zu: Navigation, Suche
K (→Einsatz von Hilfsprozeduren und Klassen aus der CodeLib)
K (Verweis auf Artikel in Access im Unternehmen)
 
(Der Versionsvergleich bezieht 40 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 in Text konvertiert werden.
+
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.
-
===Datentyp: Text===
+
==SQL-Text in Abhängigkeit des Datentyps==
-
Der Text-Filterwert muss mit ' umschlossen sein. Bei Jet/ACE ist z.B. auch " möglich.
+
===Text===
 +
Der Text-Filterwert muss mit {{Taste|'}} umschlossen sein. Bei Jet/ACE ist auch {{Taste|"}} möglich.
-
Filterwert: abc
+
SQL-Text für Filterwert: abc
<source>
<source>
... Textfeld = 'abc'
... Textfeld = 'abc'
</source>
</source>
-
Dabei ist zu beachten, dass ' im Filterwert im obigen Fall verdoppelt werden müssen, da sonst der Text zu früh als beendet markiert wird.
+
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:
-
Filterwert ist in Variable gespeichert:
+
<source>
<source>
FilterString = "Textfeld = '" & Replace(FilterWertVariable, "'", "''") & "'"
FilterString = "Textfeld = '" & Replace(FilterWertVariable, "'", "''") & "'"
</source>
</source>
-
Anm.: Die gleichen Regeln gelten natürlich auch bei Bedingungen mit Like, >=, <= usw.
+
{{remark|text=Die gleichen Regeln gelten natürlich auch bei Bedingungen mit Like, >=, <= usw.}}
-
===Datentyp: Zahlen (Integer, Double, ...)===
+
===Zahlen (Integer, Double, ...)===
Zahlen sind im amerikanischen Zahlenformat darzustellen (Punkt statt Komma).
Zahlen sind im amerikanischen Zahlenformat darzustellen (Punkt statt Komma).
-
Filterwert: 123,45
+
SQL-Text für Filterwert: 123,45
<source>
<source>
... Textfeld = 123.45
... Textfeld = 123.45
</source>
</source>
-
=>
+
VBA-Code mit in Variable gespeichertem Filterwert:
-
Filterwert ist in Variable gespeichert:
+
<source>
<source>
FilterString = "Zahlenfeld = " & Str(FilterWertVariable)
FilterString = "Zahlenfeld = " & Str(FilterWertVariable)
</source>
</source>
-
===Datentyp: Datum/Uhrzeit===
+
===Datum/Uhrzeit===
Das Datumsformat hängt vom eingesetzten DBMS ab.
Das Datumsformat hängt vom eingesetzten DBMS ab.
; Beispiele:  
; Beispiele:  
-
: Jet: #mm/dd/yyyy# oder #yyyy-mm-dd#
+
: Jet: <code>#mm/dd/yyyy#</code> oder <code>#yyyy-mm-dd#</code>
-
: MSSQL: 'yyyymmdd'
+
: MSSQL: <code>'yyyymmdd'</code>
-
Filterwert: 24.12.2015
+
SQL-Text für Filterwert: 24.12.2015
<source>
<source>
... Datumsfeld = #2015-12-24#
... Datumsfeld = #2015-12-24#
</source>
</source>
-
=>
+
VBA-Code mit in Variable gespeichertem Filterwert:
-
Filterwert ist in Variable gespeichert:
+
<source>
<source>
-
FilterString = "Datumsfeld = " & Format(FilterWertVariable, "\#yyyy-mm-dd#")
+
FilterString = "Datumsfeld = " & Format(FilterWertVariable, "\#yyyy-mm-dd\#")
</source>
</source>
Zeile 64: Zeile 63:
Davon ist abzuraten, da das z. B. nicht mit ODBC-verknüpften MSSQL-Tabellen funktioniert - wegen unterschiedlichem Datumsstartwert (Zahl 0) zw. VBA und MSSQL.
Davon ist abzuraten, da das z. B. nicht mit ODBC-verknüpften MSSQL-Tabellen funktioniert - wegen unterschiedlichem Datumsstartwert (Zahl 0) zw. VBA und MSSQL.
-
===Datentyp: Boolean===
+
===Ja/Nein, Wahr/Falsch===
-
Das Datumsformat hängt vom eingesetzten DBMS ab.
+
Das Boolean-Format hängt vom eingesetzten DBMS ab.
; Beispiele:  
; Beispiele:  
-
: Jet: True oder -1
+
: Jet: <code>True</code> oder <code>-1</code>
-
: MSSQL: 1
+
: MSSQL: <code>1</code>
-
Filterwert: True
+
SQL-Text für Filterwert: True
<source>
<source>
-
... Datumsfeld = True
+
... Booleanfeld = True
</source>
</source>
-
Anm.: Falls ODBC-verknüpfte MSSQL-Tabellen mit Bit-Feldern verwendet werden, ist von -1 als True-Wert abzuraten.
+
{{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.
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.
+
<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:
-
Filterwert ist in Variable gespeichert:
+
<source>
<source>
-
FilterString = "Datumsfeld = " & IIF(FilterWertVariable = True, "True", "False")
+
FilterString = "Booleanfeld= " & IIF(FilterWertVariable = True, "True", "False")
</source>
</source>
-
===Einsatz von Hilfsprozeduren und Klassen aus der CodeLib===
+
==Mehrere Kriterien verbinden==
-
Hilfsfunktion zur Umwandlung in Text aus [http://source.access-codelib.net/filedetails.php?repname=CodeLib+%28Entwurf%2C+branches%2Fdraft%29&path=%2Fdata%2FSqlTools.cls SqlTools]
+
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>
<source>
FilterString = "Textfeld = " & SqlTools.TextToSqlText(FilterWertVariable)  
FilterString = "Textfeld = " & SqlTools.TextToSqlText(FilterWertVariable)  
Zeile 100: Zeile 166:
</source>
</source>
-
''BuildCriteria'' aus [http://source.access-codelib.net/filedetails.php?repname=CodeLib+%28Entwurf%2C+branches%2Fdraft%29&path=%2Fdata%2FSqlTools.cls SqlTools]
+
'''''BuildCriteria'' aus {{websvn|path=data/SqlTools.cls|text=Sqltools}}'''
<source>
<source>
FilterString = SqlTools.BuildCriteria("Textfeld", SQL_Text, SQL_Equal, FilterWertVariable)
FilterString = SqlTools.BuildCriteria("Textfeld", SQL_Text, SQL_Equal, FilterWertVariable)
Zeile 114: Zeile 180:
</source>
</source>
-
Verwendung von [http://source.access-codelib.net/filedetails.php?repname=CodeLib&path=%2Fbranches%2Fdraft%2Fdata%2FFilterStringBuilder.cls FilterStringBuilder]
+
'''Klasse {{websvn|path=data/FilterStringBuilder.cls|text=FilterStringBuilder}}'''
<source>
<source>
With NewFilterStringBuilder
With NewFilterStringBuilder
-
   .Add "Textfeld", SQL_Text, SQL_Equal, FilterWertVariable
+
   .Add "Textfeld", SQL_Text, SQL_Equal, FilterWertVariable1
-
   .Add "Zahlenfeld", SQL_Numeric, SQL_Equal, FilterWertVariable
+
   .Add "Zahlenfeld", SQL_Numeric, SQL_Equal, FilterWertVariable2
-
   .Add "Datumsfeld", SQL_DateTime, SQL_Equal, FilterWertVariable
+
   .Add "Datumsfeld", SQL_DateTime, SQL_Equal, FilterWertVariable3
-
   .Add "Booleanfeld", SQL_Boolean, SQL_Equal, FilterWertVariable
+
   .Add "Booleanfeld", SQL_Boolean, SQL_Equal, FilterWertVariable4
    
    
   FilterString = .ToString
   FilterString = .ToString
Zeile 127: Zeile 193:
End With
End With
</source>
</source>
 +
 +
{{related links|links=
 +
[[ACLib-FilterForm-Wizard]]
 +
}}

Aktuelle Version vom 09:25, 15. Dez. 2015

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

SQL-Text in Abhängigkeit des Datentyps

Text

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 (Integer, Double, ...)

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)

Datum/Uhrzeit

Das Datumsformat hängt vom eingesetzten DBMS ab.

Beispiele
Jet: #mm/dd/yyyy# oder #yyyy-mm-dd#
MSSQL: '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.

Ja/Nein, Wahr/Falsch

Das Boolean-Format hängt vom eingesetzten DBMS ab.

Beispiele
Jet: True oder -1
MSSQL: 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")

Mehrere Kriterien verbinden

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)

Einsatz von Hilfsprozeduren und Klassen aus der CodeLib

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

Von „http://wiki.access-codelib.net/SQL-Text_f%C3%BCr_Filterbedingung“