compare two dates

[joyo]

Hi,

Below is my code, I don't know why it doesn't work.
I think the problem is in "*" line, but I can not figure
it out. I also tried to add "#" between the date field.

Private Sub RemoveButton_Click()
On Error GoTo Err_RemoveButton_Click
Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Holidays",
dbOpenDynaset)
Dim SelectedItemIndex As Integer
SelectedItemIndex = Me.HolidayList.ListIndex

If Not (SelectedItemIndex) = -1 Then
Do Until rs.EOF
******* If rs!HoliDate = Me.HolidayList.Column
(0, SelectedItemIndex) Then
rs.Delete
End If
rs.MovePrevious
Loop
Else
MsgBox "You need to select a item first"
End If
Exit_RemoveButton_Click:
Exit Sub
Err_RemoveButton_Click:
MsgBox Err.Description
Resume Exit_RemoveButton_Click
End Sub


Thanks

joyo

 

[John Smith]

I am not sure, but suspect that Access may be doing a string comparison, rather
than a date comparison - it has caught me that way before now. Try :-

Private Sub RemoveButton_Click()
On Error GoTo Err_RemoveButton_Click
Dim SQL As String
Dim SelectedItemIndex As Integer
SelectedItemIndex = Me.HolidayList.ListIndex
If Not (SelectedItemIndex) = -1 Then
SQL = "DELETE FROM tbl_Holidays" _
& " WHERE HoliDate = #" & HolidayList.Column(0, SelectedItemIndex) &
"#"
Currentdb.Execute SQL, dbFailOnError
Else
MsgBox "You need to select a item first"
End If
Exit_RemoveButton_Click:
Exit Sub
Err_RemoveButton_Click:
MsgBox Err.Description
Resume Exit_RemoveButton_Click
End Sub

Assuming that Column(0) contains a valid date it should work, and also be
faster.