2nd business day of the month

[Guest]

I am trying to automate the emailing of a report on the 2nd business day of
the month. This is the only day in which the report would be sent. It is
currently being generated manually.

Does anyone know how to syntax for determining the 2nd business day. Since
this day will not always be the 2nd day of the month, how do I compensate for
that.

Thanks for your help....

 

[walkbet]

You can try using the Day, Weekday and WeekdayName functions to help
with this.

the Day function filtered to be within the first week of the month:

Day(date()) <8


The weekday & weekname function nested to figure out the day of the
week (I'm assuming you will always want a Tuesday as the 2nd business
day of any week). This function sees Sunday as "1", so Tuesday is "3":

WeekdayName(Weekday(date())) = 3

Note the closing of all parenthesis, especially on the 2nd expression.

I tested these in a query and they worked just fine. Not sure where
your expressions will be written, but I hope this give you some helpful
ideas,

Betsy

 

[Guest]

Betsy,

What if the 2nd business day of the month is a Wednesday (or Thursday or
Friday for that matter). This is where I got stuck. It almost seems like I
need to timestamp the process to help identify the day.

Let me work up some code and I'll post for you to see. (maybe you can post
yours from your query).

Thanks.

 

[walkbet]

As a correction to my suggestion posted above. You don't need the
WeekDayName function at all. Just this:


Weekday(date()) = 3


That will identify any Tuesday.

Sorry for the confusion...

hth-

Betsy

 

[walkbet]

Ah, I see the problem. So on weeks where Monday is a holiday, you'd
need Wednesday to be the 2nd business day, etc. Hmmm. I didn't take
that into consideration. I guess that I'd try maintaining a table of
"exception weeks" which each would have an offset number to adjust your
expression. Create this table for the entire year now. Then your
query or code would have to check against that table to see if the day
of the particular week is not a regular one. This table would have to
be adjusted at the beginning of each year, I think.

Off the top of my head, without testing, that's the direction I'd work
in.

Betsy

 

[Guest]

Betsy,

Here is my code:

If Day(Now()) < 8 Then
If Weekday(Now()) = 3 Then
DoCmd.SendObject acQuery, "Qtr End Reporting", _
"MicrosoftExcelBiff8(*.xls)", "", "", "", _
"Quarter End Reporting", strText, False, ""
Else
Exit Function

End If
End If

All works great on Tuesday (or any other day I indicate). The problem is
that the 2nd business day is not constant. It will differ month to month.
Is there a way to determine if any day is in deed the 2nd business day of the
month. This is why I am thinking a time stamp. If the time stamp exists for
this month, do not sent. Any thoughts...

 

[aaron.kempf]

1) use access data projects instead of silly mdb stuff
2) setup a sql job to email a query once a month
3) make sure the sqlagent service is running

ps - dont ever use the word timestamp all you do is confuse the shit
out of people

 

[James A. Fortune]

I am trying to automate the emailing of a report on the 2nd business day of
the month. This is the only day in which the report would be sent. It is
currently being generated manually.

Does anyone know how to syntax for determining the 2nd business day. Since
this day will not always be the 2nd day of the month, how do I compensate for
that.

Thanks for your help....

See:

http://groups.google.com/group/comp...163f0/0c4a7b279d4af0d7?hl=en#0c4a7b279d4af0d7

You can use this technique to find the second business day in a month by
using the first date of the month: DateSerial(Year(Date()),
Month(Date()), 1) as the StartDate. Date() here is only used as an
example. You can use DateAdd("d", 1, StartDate) as the first EndDate
(maybe even StartDate + 1 would work instead) and increment until the
number of workdays = 2. There's probably a direct way to get it without
incrementing the EndDate but see if this works for what you need for
now. Note that this technique makes use of a tblHoliday until I think
of an improved way to count the number of holidays in a given date range
using holiday functions. I also need to point out that the basic
technique in the cited post has not been tested much. I like the name
"business days." It sounds much more professional than "workdays."

I hope this helps,

James A. Fortune
(e-mail address removed)