Automatically Generate Reports on Last Working Day Of Month

[Harlequin007]

Morning guys.

I have an issue with outputting data from Access to Excel you might be able to help with.

What I have done is developed a macro (so designated users can edit it) that auto outputs a query to excel.

I'd like to do this on the last working day of the month. preferably without using VB. Although if I have to I will use VB.

Any ideas...?

----------------------------
Cogito Ergo Sum

http://www.discworks.co.uk

----------------------------

 

[FancyPrairie]

I use Window's task scheduler to schedule tasks. You'll find it under Accessories|System Tools|Scheduled Tasks. The properties for the task would look something like this:

\\path\msaccess.exe \\path\YourAccessDb.mdb /x YourMacroName

 

[Harlequin007]

Will that work for the last "working" day in a month Fancy...?

I'd prefer to manage it from within the DB than put a setting on a specific user's PC.

----------------------------
Cogito Ergo Sum

http://www.discworks.co.uk

----------------------------

 

[nabiS2K]

You will need to use VB. Here is a function you can use in a module to find the last work date of a month. You can then use it in other code or even in a query...

Function GetLastWorkDate(Optional YourDate As Date = #1/1/1900#) As Date

   Dim IsWorkDay As Boolean
   Dim CurDate As Date
     
   If YourDate  = #1/1/1900# Then
      CurDate = Now
   Else
      CurDate = YourDate
   End If

  'Find the last day of the month
   If Month(CurDate) = 12 Then
      CurDate = CDate("1/1/" & Year(CurDate) + 1) - 1
   Else 
      CurDate = CDate((Month(CurDate) + 1) & "/1/" & Year(CurDate)) - 1
   End If

   'Work backwards form the last day of the month to find the 
   'first business day
   Do 

      Select Case Format(CurDate, "dddd")
         Case "Monday", "Tuesday", "Wednesday", Thursday", "Friday"
            IsWorkDay = True
         Case Else
            CurDate = CurDate - 1
      End Select
   Loop Until IsWorkDay

   GetLastWorkDate = CurDate

End Function

Let me know if you need anymore help...

 

[Harlequin007]

Nabi.

Thanks for that. I'll integrate it and it should do exactly what I want...

----------------------------
Cogito Ergo Sum

http://www.discworks.co.uk

----------------------------