Help on If .... Then

[gear]

On the first day of January every year, I want to run a module to backup DB and then delete all records. If the date is not 1st January, then it should give a message. I want to achieve this with the click of button. I put the following code in the OnClick event of the button but it is not working. Could you please suggest a remedy?

If Date = 1 - 1 - Year(Date) Then

BackupAndZipit

Dim db As Database, rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAllDet")

db.Execute "DELETE * FROM [tblAllDet];"
rs.Close
db.Close

Else
End If

Dim intResponse As Integer
Dim strMsg As String

strMsg = "This action cannot be executed now"
intResponse = MsgBox(strMsg, vbOKOnly)
If intResponse = vbOK Then

DoCmd.Close
Else
End If

 

[John_W]

Have you considered what will happen if you forget to do it on 1st Jan? (You might not feel up to it after the night before!)

 

[Steve R.]

In Access visual basic help, lookup DatePart Function.

Also under the "See Also" option, you will see other date functions that could be used.

My initial reaction is that you need to format DATE on the left hand side of the equation to be a short date in the form 1/1/2007. Also the right hand side of the equation also needs to be formated as a date value.

Since January =1 and the first day of January =1, it may be easier to use DatePart and test to see if it equals 2.

if DatePart("m", now()) + DatePart("d", Now()) =2 then
    your code
end if

-----------------------------------------------------------------------
After thinking a bit more, this may be somewhat better and more flexible.

if DatePart("m", now()) =1 AND DatePart("d", Now()) =1 then
    your code
end if

 

[gear]

Wonderful!!

Wonderful, Sir, wonderful!! Your code worked. Thank you very much.

 

[gear]

Mr John W,
I appreciate the concern expressed by you.
My DB is locked after 31st Dec and no data entry is possible unless the above command is executed.
Thanks