ms access delete records older than six months [Resolved]

[wpearsall]

ok, my database connection is working fine, can delete etc.

need to mass delete tho, all records older than six months i wanna delete.

pretty simple, except it isnt happening, im thinking problem wi me query.

Private Sub mnuFileClear_Click()
    
    If MsgBox("Are you sure you wish to delete old session data?" & vbCrLf & _
              vbCrLf & "This will remove session data older than six months!", _
              vbQuestion + vbYesNo + vbDefaultButton2) = vbNo Then Exit Sub
              
    Dim lRecCount As Long, sQuery As String
        
    sQuery = "DELETE FROM Visits WHERE sDate<" & _
             FormatDateTime(DateAdd("m", -6, Now), vbShortDate) & _
             ";"
             
             Debug.Print sQuery
    ConnOBData.Execute sQuery, lRecCount
    
    Debug.Print lRecCount & " affected records!"
    
End Sub

DELETE FROM Visits WHERE sDate<09/09/2006;
0 affected records!

anyone spot the error? :S meh. i av got records wi the dates in march,april,may,june in 2005/2006 so should work? :S

ta

[Mark Gambo]

You have to surround the date value with the # signs:

Dim lRecCount As Long, sQuery As String
 
sQuery = "DELETE FROM Visits WHERE sDate < #" & _
FormatDateTime(DateAdd("m", -6, Now), vbShortDate) & _
"#;"

[wpearsall]

cheers mate, that did it. [i knew it was gonna b somethin simple lol :S ta

[Mark Gambo]

Any time