Last Friday of the month notification 1

[jgarnick]

I need to alert the user to run a particular report on the last Friday of every month--anybody know how to test the date to see if it's the last FRIDAY? Can you use the Dateserial function?&nbsp;&nbsp;If so, what's the syntax?<br><br>Thanks! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[DougP]

Public Function LastFridayOfMonth()<br>&nbsp;&nbsp;&nbsp;&nbsp;' This is only for the current month<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim LastDayOfMonth As Date, LastDayOfWeek, DaysToSubtract&nbsp;&nbsp;As Integer<br><br>&nbsp;&nbsp;&nbsp;&nbsp;LastDayOfMonth = DateSerial(Year(Now), Month(Now) + 1, 1 - 1)<br><br>&nbsp;&nbsp;&nbsp;&nbsp;' Get the Day of the week for the last Day. Wednesday will return a 4<br>&nbsp;&nbsp;&nbsp;&nbsp;LastDayOfWeek = Val(Format(LastDayOfMonth, &quot;w&quot)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Select Case LastDayOfWeek<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Day is a Sunday<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DaysToSubtract = 2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Day is a Monday<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DaysToSubtract = 3<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 3<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Day is a Tuesday<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DaysToSubtract = 4<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 4<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Day is a&nbsp;&nbsp;Wednes<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DaysToSubtract = 5<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 5<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Day is a Thur<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DaysToSubtract = 6<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 6<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Day is a Fri<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DaysToSubtract = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 7<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Day is a Sat<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DaysToSubtract = 1<br><br>&nbsp;&nbsp;&nbsp;&nbsp;End Select<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;LastFridayOfMonth = DateSerial(Year(LastDayOfMonth), Month(LastDayOfMonth), Day(LastDayOfMonth) <br><br>- DaysToSubtract)<br><br>End Function<br>&nbsp;<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[jgarnick]

Thanks DougP--worked like a charm.......<br><br>Do you know how I can get my message box that warns to run the report show up on Monday if the user is out of work on Friday?&nbsp;&nbsp;How do you add a check box to the warning that says &quot;Don't show this warning again&quot; or something like that--something that allows the user to turn off the warning once they see it (or not turn it off right away if they need a reminder for the rest of the day) until the next month.&nbsp;&nbsp;Right now I have if date=lastfridayofmonth, msgbox etc.<br><br>Thanks! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[DougP]

That's another thing altogether.<br>You need to have a Table which you store a value in.<br>I'd call the table &quot;Settings&quot; and it would have just one field called &quot;DidWeRun&quot; whose Field type is a Yes/No. The table has only one record which gets over written everytime the report actually printed.<br>So In other words no matter which day it was opened the table knows if it was run or not.<br><br>In your Function you would check the value and reset it when the person printed. I would create a second function that calls your report and the above &quot;LastFridayOfMonth&quot;<br>Then it (the table) would know what's going on without having a message box at all.<br>The second function is called from an &quot;Autoexec&quot; macro<br>If you create a macro and name it exactly &quot;Autoexec&quot; it will run first, everytime the database is opened.<br>This macro calls your second Function. Lets call it RunReport(). <br><br>1st Create a Function called RunReport() and save it.<br>2nd Create a macro called autoexec (in the top box of the macro look for &quot;Run Code&quot; then in the lower part of the macro screen is a box called &quot;Funtion Name&quot; click the 3 dots button an you will see your new Function.<br><br>I did this very thing but it was totally automated. The database was actually in the users &quot;Start&quot; folder so when the PC was re-booted it opened the database minimized and checked to see if the report was run or not. If not, it just printed the report and a message poped up that said &quot;Report printed in CEO's office&quot;. The report was set to go to his printer. If the database was opened again the macro still ran but it saw that the report was printed and did not do it again until it was Monday morning again. Or whenever there after the database was opened.<br>----------------------- Very Brief -------------<br>Public Function RunReport()<br>&nbsp;&nbsp;&nbsp;&nbsp;' This function checks to See if report is run<br>&nbsp;&nbsp;&nbsp;&nbsp;' open table<br>&nbsp;&nbsp;&nbsp;&nbsp;If rst.DidWeRun = True Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Don't run report<br>&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.OpenReport &quot;ReportName&quot;, acViewPreview<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br><br>'close table<br><br>End Function<br>--------------------------------<br>I can write this function for you if you e-mail me.<br><br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.