Need help with custom sql function

[Imperceptus]

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.

Code Select Expand


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




[Imperceptus]

Better Logic, but it could be done better.  Thanks to all you guys for the wonderful help.

Code Select Expand


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