Good day Folks,
I'm hoping someone can assist me with a problem I am having with regards to the last working day of the current month.
I obtained a module called Lastworkingday from this forum and it works fine in that it identifies the lastworkingday of the month, the problem arises when I want to check weather the current day is the last working day of the month. Nothing happens at the end of the month on the last working day.
I thought that by comparing the Current date with the lastworkingday date it would action the code that followed, so I believe that I have done something wrong in my code, but can't work out what.
Below is the code that I have written which is supposed to run only if the current day is the last working day of the month:
=================
START CODE
=================
Dim LastWorkDayOfMonth 'Declare LastWorkDayOfMonth variable
Dim dtmDate 'Declare dtmDate variable
Dim CurrentDay 'Declare CurrentDay variable
CurrentDay = Format(Now(), "dd/mm/yy")
Call LastWorkDay 'Call the LastWorkDay Function
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 = CurrentDay 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:\ADDLP\Commercial\Commercial" & "_" & Format(Date, "ddmmyy") & ".txt", False, ""
'Delete the data from the tblCommercial table
DoCmd.OpenQuery "qryDelCommercial", acNormal, acEdit
End If
===================
END CODE
===================
Below is the Lastworkingday code that I obtained from this forum:
==================
START 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
================
Any assistance would be most appreciated.
John
I'm hoping someone can assist me with a problem I am having with regards to the last working day of the current month.
I obtained a module called Lastworkingday from this forum and it works fine in that it identifies the lastworkingday of the month, the problem arises when I want to check weather the current day is the last working day of the month. Nothing happens at the end of the month on the last working day.
I thought that by comparing the Current date with the lastworkingday date it would action the code that followed, so I believe that I have done something wrong in my code, but can't work out what.
Below is the code that I have written which is supposed to run only if the current day is the last working day of the month:
=================
START CODE
=================
Dim LastWorkDayOfMonth 'Declare LastWorkDayOfMonth variable
Dim dtmDate 'Declare dtmDate variable
Dim CurrentDay 'Declare CurrentDay variable
CurrentDay = Format(Now(), "dd/mm/yy")
Call LastWorkDay 'Call the LastWorkDay Function
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 = CurrentDay 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:\ADDLP\Commercial\Commercial" & "_" & Format(Date, "ddmmyy") & ".txt", False, ""
'Delete the data from the tblCommercial table
DoCmd.OpenQuery "qryDelCommercial", acNormal, acEdit
End If
===================
END CODE
===================
Below is the Lastworkingday code that I obtained from this forum:
==================
START 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
================
Any assistance would be most appreciated.
John
