delete sql not working.

hi

win 2k and xp
excel 2k
sqlserver version 7
the code below execute but when i query the table, the data is still in there. can anyone help?

Sub UPDATED_DELETE()

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
dim MyDate As Date

MyDate = Format(Date, "MM/DD/YYYY")

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command

conn.ConnectionString = "ODBC=SQL Server;DSN=LOGCALL_TABLE;UID=richard;APP=Microsoft Query;WSID=RICHARD;Trusted_Connection=Yes"
conn.ConnectionTimeout = 30
conn.Open

Set cmd.ActiveConnection = conn

cmd.CommandText = "DELETE FROM LOGCALL_TABLE WHERE LOGCALL_TABLE.OpenCall like 'X' AND LOGCALL_TABLE.StopTime like '" & Format(Range("I" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.EndTime like '" & Format(Range("J" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.ClientName like '" & Range("B" & CStr(ActiveCell.Row)).Value & "' AND LOGCALL_TABLE.Representative like '" & Range("C1").Value & "' and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute
conn.Close
End SubI think it has to do with the date and the way it is formated in my sql statement.

.......and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute

i had it formated this way before as required by ms access, but that does not work.

..... and LOGCALL_DB.DateOnCall = # " & Date & " #;"

any thoughts on how to format this baby?

thanks in advance.

Alex|||I think it has to do with the date and the way it is formated in my sql statement.

.......and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute

i had it formated this way before as required by ms access, but that does not work.

..... and LOGCALL_DB.DateOnCall = # " & Date & " #;"

any thoughts on how to format this baby?

thanks in advance.

Alex|||to begin with, LIKE should only be used with strings

what is DateOnCall? datetime or varchar?|||Be smart and create a stored procedure in you SQL Server database that accepts StopTime, EndTime, ClientName, Representative, and DateOnCall as parameters and deletes the records you want. Then just call the procedure with the values from your spreadsheet.

...and look up the syntax and usage of the LIKE operator too. I suspect it needs wildcards, or at the very least is inappropriate for Date values (as Rudy said).

But I really think the problem is in your methodology, not your syntax...