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]
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


[/code]

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

[code]
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
[/code]