Option Explicit
Public Sub test()
Dim arrFilter(1, 4) As String
arrFilter(0, 0) = "Field_Name"
arrFilter(0, 1) = "Value"
arrFilter(0, 2) = "False"
arrFilter(0, 3) = "="
Debug.Print sql_filter(arrFilter)
End Sub
Public Function sql_Value(ByVal Input_Value As String, Optional ConvertToText As Boolean) As String
Dim tmp As String
tmp = Replace(Input_Value, ";", ",", Compare:=vbTextCompare)
If tmp = vbNullString Then
sql_Value = "NULL"
ElseIf IsNumeric(tmp) Then
If ConvertToText Then
sql_Value = "'" & tmp & "'"
Else
Dim regEx As New RegExp
regEx.Pattern = "[0-9]?[0-9][.][0-9]?[0-9][.][0-9][0-9][0-9][0-9]"
If regEx.test(tmp) Then
sql_Value = sql_Date(tmp)
Else
sql_Value = "'" & tmp & "'"
End If
End If
ElseIf tmp = "Wahr" Or tmp = "Falsch" Then
sql_Value = sql_Boolean(tmp)
Else
sql_Value = "'" & tmp & "'"
End If
End Function
Private Function sql_Boolean(ByVal Wert As String) As String
Select Case Wert
Case "Wahr"
sql_Boolean = -1
Case "Falsch"
sql_Boolean = 0
End Select
End Function
Private Function sql_Date(ByVal Datum As Date) As String
sql_Date = Format(Datum, "\#yyyy-mm-dd hh:nn:ss\#")
End Function
Public Function sql_filter(ByRef strValues() As String, Optional ByVal order_field As String, Optional ByVal sort_asc As Boolean) As String
Dim i As Integer, j As Integer
For i = 0 To UBound(strValues(), 1)
If Not strValues(i, 1) = "" Then
If j = 0 Then
If strValues(i, 1) = "IS NULL" Or strValues(i, 1) = "IS NOT NULL" Then
sql_filter = " WHERE " & strValues(i, 0) & " " & strValues(i, 1)
ElseIf InStr(1, strValues(i, 1), "*", vbTextCompare) <> 0 Then
sql_filter = " WHERE " & strValues(i, 0) & " LIKE " & sql_Value(strValues(i, 1), CBool(strValues(i, 2)))
ElseIf strValues(i, 3) = "" Then
sql_filter = " WHERE " & strValues(i, 0) & " = " & sql_Value(strValues(i, 1), CBool(strValues(i, 2)))
Else
sql_filter = " WHERE " & strValues(i, 0) & " " & strValues(i, 3) & " " & sql_Value(strValues(i, 1), CBool(strValues(i, 2)))
End If
Else
If strValues(i, 1) = "IS NULL" Or strValues(i, 1) = "IS NOT NULL" Then
sql_filter = sql_filter & " AND " & strValues(i, 0) & " " & strValues(i, 1)
ElseIf InStr(1, strValues(i, 1), "*", vbTextCompare) <> 0 Then
sql_filter = sql_filter & " AND " & strValues(i, 0) & " LIKE " & sql_Value(strValues(i, 1), CBool(strValues(i, 2)))
ElseIf strValues(i, 3) = "" Then
sql_filter = sql_filter & " AND " & strValues(i, 0) & " = " & sql_Value(strValues(i, 1), CBool(strValues(i, 2)))
Else
sql_filter = sql_filter & " AND " & strValues(i, 0) & " " & strValues(i, 3) & " " & sql_Value(strValues(i, 1), CBool(strValues(i, 2)))
End If
End If
j = j + 1
End If
Next i
If order_field = "" Then
sql_filter = sql_filter & ";"
Else
If sort_asc Then
sql_filter = sql_filter & " ORDER BY " & order_field & " ASC;"
Else
sql_filter = sql_filter & " ORDER BY " & order_field & " DESC;"
End If
End If
End Function