Im working with JET and trying to make a function that takes a current sql sentence and removes the field i send it. I have made more then a few attempts at this and Im still failing. Can anyone offer some advice or a solution?
Heres my last attempt.
Public Function RemoveFieldFromSQL(ByVal sqlcommand As String, ByVal myFieldName As Variant, ByVal MyFieldValue As Variant) As String
Dim StrSplit() As String, EqualsSplit() As String, OldString() As String, Pos As Integer, N As Integer
sqlcommand = Replace(sqlcommand, ";", "")
OldString = Split(sqlcommand, "WHERE")
StrSplit = Split(Trim(OldString(1)), " ")
For N = LBound(StrSplit) To UBound(StrSplit)
EqualsSplit = Split(StrSplit(N), "=")
If UBound(EqualsSplit) > 0 Then
If EqualsSplit(0) = "[" & myFieldName & "]" Then
If EqualsSplit(1) = "'" & MyFieldValue & "'" Then
If N > 0 Then
Select Case StrSplit(N - 1)
Case "OR"
StrSplit(N - 1) = ""
Case "AND"
StrSplit(N - 1) = ""
Case Else
Select Case StrSplit(N + 1)
Case "OR"
StrSplit(N + 1) = ""
Case "AND"
StrSplit(N + 1) = ""
End Select
End Select
StrSplit(N) = ""
Else
StrSplit(N) = ""
End If
GoTo 1
End If
End If
End If
Next N
exit function
1:
sqlcommand = OldString(0) & "WHERE" & Space$(1)
For N = LBound(StrSplit) To UBound(StrSplit)
sqlcommand = sqlcommand & Space$(1) & StrSplit(N)
Next N
RemoveFieldFromSQL = Trim(sqlcommand) & ";"
End Function
Better Logic, but it could be done better. Thanks to all you guys for the wonderful help.
Public Function RemoveFieldFromSQL(ByVal sqlcommand As String, ByVal myFieldName As Variant, ByVal MyFieldValue As Variant) As String
Dim StrSplit() As String, EqualsSplit() As String, OldString() As String, Pos As Integer, N As Integer
sqlcommand = RTrim(Replace(sqlcommand, ";", ""))
StrSplit = Split(sqlcommand, myFieldName + "]='")
For N = LBound(StrSplit) + 1 To UBound(StrSplit)
If MyFieldValue = Left(StrSplit(N), InStr(1, StrSplit(N), "'") - 1) Then
If InStr(1, StrSplit(N), MyFieldValue + "' OR") Or InStr(1, StrSplit(N), MyFieldValue + "' AND") Then
If N = UBound(StrSplit) Then 'Last element of this field
sqlcommand = Replace(sqlcommand, " OR [" + myFieldName + "]='" + MyFieldValue + "'", "") + ";"
sqlcommand = Replace(sqlcommand, " AND [" + myFieldName + "]='" + MyFieldValue + "'", "")
Else
sqlcommand = Replace(sqlcommand, "[" + myFieldName + "]='" + MyFieldValue + "' OR ", "") + ";"
sqlcommand = Replace(sqlcommand, "[" + myFieldName + "]='" + MyFieldValue + "' AND ", "")
End If
Exit For
Else
If UBound(StrSplit) = 1 Then 'ONLY 1 ELEMENT
RemoveFieldFromSQL = Replace(sqlcommand, " WHERE [" + myFieldName + "]='" + MyFieldValue + "'", "") + ";"
Else 'Last Element of all fields
sqlcommand = Replace(sqlcommand, " OR [" + myFieldName + "]='" + MyFieldValue + "'", "") + ";"
sqlcommand = Replace(sqlcommand, " AND [" + myFieldName + "]='" + MyFieldValue + "'", "")
End If
End If
End If
Next N
RemoveFieldFromSQL = sqlcommand
End Function