Last Working Day (1 Viewer)

[JohnLee]

Hi Folks,

I have some code which I obtained from this forum, which I have included in my database as a module and the code is as follows:

'================
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
===============

My module is called modLastWorkDay. I have written the following code in my progress form:

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

Dim LastWorkDayOfMonth 'Declare LastWorkDayOfMonth variable
Dim dtmDate 'Declare dtmDate variable
Dim CurrentDay 'Declare CurrentDay variable

CurrentDay = Format(Date, "dd/mm/yy") 'Assign the current date to the CurrentDay variable

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:\Commercial\Commercial" & "_" & Format(Date, "ddmmyy") & ".txt", False, ""
'Delete the data from the tblCommercial table
DoCmd.OpenQuery "qryDelCommercial", acNormal, acEdit
End If
==============
END CODE
==============

The problem is, that whenever it is the last working day of the month the code doesn't print my reports or output my text file! So clearly there is something wrong with the way my code is written. Any help would be most appreciated.

John

 

[Rabbie]

At the moment you have LastWorkDayOfMonth in date format and Currentday in string format.

Try this code

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

Hope this helps

 

[DCrake]

Think you need to delcare CurrentDate as a Date not a variant

Call LastWorkDay

This is not doing anything useful as it is repeated on the next line

LastWorkDayOfMonth = LastWorkDay

Change

If LastWorkDayOfMonth = CurrentDay Then

To

If LastWorkDayOfMonth = Date() Then

 

[JohnLee]

Hi DCrake & Rabbie,

Thanks for your responses, I've applied your suggestions. Is there a way I can test this now or is it best to wait until the last work day to see if it actions?

John