1st of every month

[hrov]

can someone write me a code to:

Print off certain reports on the 1st of every month?

E.g. 5 different reports that we can call "report1" "report2" etc for simplicity.

 

[HiTechCoach]

Here are some pieces that I use that should help:

Reminders

and

How to Start Access by Using the Windows NT Schedule Service

 

[JohnLee]

Hi,

This is my code I wrote which was used in conjunction with code I obtained from this forum:

My Code [Modify to your requirements]

=========
BEGIN CODE
=========

Dim LastWorkDayOfMonth As Date 'Declare LastWorkDayOfMonth variable

LastWorkDayOfMonth = LastWorkDay() 'Assign the value of LastWorkDay to the LastWorkDayOfMonth variable

'If the current date ie equal to the LastWork Day Of The Month then
If LastWorkDayOfMonth = Date Then
'Print the Monthly Letter Type Chart
DoCmd.OpenReport "rptChartMonthlyLetterCount", acNormal, "", ""
DoCmd.OpenReport "rptStatsMonth", acNormal, "", ""
'Append Monthly data to tblCommercial table
DoCmd.OpenQuery "qryAppCommercial", acNormal, acEdit
'Export the contents of the tblCommercial table to the Commerical folder on the Question on 'Leint03' drive with the current date
DoCmd.TransferText acExportFixed, "CommercialExportSpec", "tblCommercial", "E:\Commercial\Commercial" & "_" & Format(Date, "ddmmyy") & ".txt", False, ""
'Delete the data from the tblCommercial table
DoCmd.OpenQuery "qryDelCommercial", acNormal, acEdit
End If

=========
END CODE
=========

Code obtained from this forum, for use with the above

=========
BEGIN CODE
=========


Public Function LastWorkDay() As Date
'-- Return the last working day of the current month

Dim Searching As Boolean
Searching = True

LastWorkDay = DateSerial(Year(Date), Month(Date) + 1, 0) '-- Start at the last day of the month
Do While Searching
If Weekday(LastWorkDay, vbMonday) > 5 Then
'-- Weekend day, back up a day
LastWorkDay = LastWorkDay - 1
Else
'-- If you have a Holiday Table then enable the next IF...Else
' If Weekday(LastWorkday, vbMonday) > 5 Or _
Not IsNull(DLookup("[HoliDate]", "tblHolidays", _
"[HoliDate] = " & Format(LastWorkday, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
'-- The above Format of LastWorkday works with US or UK dates!
' LastWorkday = LastWorkday - 1
' Else
'-- The search is over
Searching = False
End If
Loop

End Function

=========
END CODE
=========

I hope this helps

John

 

[hrov]

Hi,

This is my code I wrote which was used in conjunction with code I obtained from this forum:

My Code [Modify to your requirements]

=========
BEGIN CODE
=========

Dim LastWorkDayOfMonth As Date 'Declare LastWorkDayOfMonth variable

LastWorkDayOfMonth = LastWorkDay() 'Assign the value of LastWorkDay to the LastWorkDayOfMonth variable

'If the current date ie equal to the LastWork Day Of The Month then
If LastWorkDayOfMonth = Date Then
'Print the Monthly Letter Type Chart
DoCmd.OpenReport "rptChartMonthlyLetterCount", acNormal, "", ""
DoCmd.OpenReport "rptStatsMonth", acNormal, "", ""
'Append Monthly data to tblCommercial table
DoCmd.OpenQuery "qryAppCommercial", acNormal, acEdit
'Export the contents of the tblCommercial table to the Commerical folder on the Question on 'Leint03' drive with the current date
DoCmd.TransferText acExportFixed, "CommercialExportSpec", "tblCommercial", "E:\Commercial\Commercial" & "_" & Format(Date, "ddmmyy") & ".txt", False, ""
'Delete the data from the tblCommercial table
DoCmd.OpenQuery "qryDelCommercial", acNormal, acEdit
End If

=========
END CODE
=========

Code obtained from this forum, for use with the above

=========
BEGIN CODE
=========


Public Function LastWorkDay() As Date
'-- Return the last working day of the current month

Dim Searching As Boolean
Searching = True

LastWorkDay = DateSerial(Year(Date), Month(Date) + 1, 0) '-- Start at the last day of the month
Do While Searching
If Weekday(LastWorkDay, vbMonday) > 5 Then
'-- Weekend day, back up a day
LastWorkDay = LastWorkDay - 1
Else
'-- If you have a Holiday Table then enable the next IF...Else
' If Weekday(LastWorkday, vbMonday) > 5 Or _
Not IsNull(DLookup("[HoliDate]", "tblHolidays", _
"[HoliDate] = " & Format(LastWorkday, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
'-- The above Format of LastWorkday works with US or UK dates!
' LastWorkday = LastWorkday - 1
' Else
'-- The search is over
Searching = False
End If
Loop

End Function

=========
END CODE
=========

I hope this helps

John

John that seems way too complicated for me, although it looks similar to what i am looking for:

all i want is the code to say
If today is a monday then run "macro 1"

surely this doesnt need to be too complicated. ( i have changed from the 1st of every month, to every monday as the database may not be ran on the 1st)

 

[JohnLee]

Hi,

If you search this forum using the words "Day of the Week" you'll find a few posts that involve this aspect of what your looking for.

John