Rory

Well it would, if you actually called the second function. Also the second function, as it name implies, doesn't do the deleting it just checks whether the record is more than a month old. You need to write that bit of the code!

kbkhoo5053

Rory, I am not understand what do u mean by second function? How to write it? Can you guide me? Thanks.

Rory

Well all that should work if you actually write the code to delete the record! Also I assume you need an if statement around the msgbox(). If you want any more help, you'll have to post a bit more of the source - I'm still not fully clear what you're asking

kbkhoo5053

Rory, i add in if statement around the msgbox(). Can u help me check whether there is error or not? Can? Thanks. 2molo i need to submit my program to my supervisor. Plz help me ensure the full program below. Thanks you very much.

Private Sub cmdDelete_Click()

datCheckin.DatabaseName = App.Path & "\db.mdb"
sSql = "select * from checkin"
datCheckin.RecordSource = sSql
datCheckin.Refresh

datCheckout.DatabaseName = App.Path & "\db.mdb"
sSql1 = "select * from checkout"
datCheckout.RecordSource = sSql1
datCheckout.Refresh

datRoom.DatabaseName = App.Path & "\db.mdb"
sSql2 = "select * from room"
datRoom.RecordSource = sSql2
datRoom.Refresh

datDaily.DatabaseName = App.Path & "\db.mdb"
sSql3 = "select * from daily"
datDaily.RecordSource = sSql3
datDaily.Refresh

End Sub

Public Function CanRecordBeDeleted(ByVal strRecordDate As String) As Boolean

' Inputs: A string date
' Outputs: True or false
' True: Date occurs in last month or prior
' False: Date occurs this month or later
'
Dim datRecordDate As Date
' Used to hold the date in "VB" form
Dim datStartOfMonth As Date
' Date rolled back to the start of the month
Dim TodayStartOfMonth As Date
' Today rolled back to the start of the month

datRecordDate = CDate(strRecordDate)

datStartOfMonth = VBA.DatePart("m", datRecordDate)
TodayStartOfMonth = VBA.DatePart("m", VBA.Now())
' This built in VB function rounds down to the start of the month

If datStartOfMonth < TodayStartOfMonth Then
CanRecordBeDeleted = True
Else
CanRecordBeDeleted = False
End If

if DateDiff("date", datein, dateout) >=30 then

MsgBox("Do you really want to clear all the shift records ?", vbExclamation + vbYesNo, "DELETE ALERT") = vbYes

End Function

The code is only getting the months? How about dates such as "1/1/1900"?
How to is include the year in the comparison?

Rory, I test it, no errors. However, I still can't get my answer. It cannot delete the shift report. No changes even thought I insert the coding that u gav me. It still remains same. How? Can u help me?