Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Find the 5th business day

Posted on 2006-03-29
Medium Priority
411 Views

Hello experts,

In MS Access I need a VBA code to check if today is the 5th business day of the month.

Thanks.
0
Question by:LenkaL
• 3
• 2
• 2
• +1

LVL 34

Expert Comment

ID: 16323330
Wont it always be the 7th of the month?  Unless you are including bank/public holidays.. Are you counting these?
0

LVL 14

Expert Comment

ID: 16323341
Could also be the 5th or 6th, if the month starts on a Sun or Mon.  I'm trying to work out the logic.
0

LVL 34

Expert Comment

ID: 16323386
ahh. Sure is

0

LVL 6

Expert Comment

ID: 16323402
Check out this link that contains a formula to find the nth particular weekday of any month:

0

LVL 14

Accepted Solution

JohnK813 earned 200 total points
ID: 16323423
Here's what I came up with:

If today is Friday, and the date is the 5th or the 6th, it's the 5th bus day of the month.
If the above is not true, but today is the 7th (and it's not a weekend), it's the 5th bus day of the month.
Otherwise, it's not the 5th bus date.

To use the function below, call

SomeBooleanVariable = IsFifthBusDay(DateYouWantToCheck)

It will return True if it is the 5th bus day, False otherwise.

-----

Private Function IsFifthBusDay(YourDate as Date)
If WeekDay(YourDate) = vbFriday AND (Day(YourDate) = 5 OR Day(YourDate) = 6) Then
IsFifthBusDay = True
ElseIf Day(YourDate) = 7 AND WeekDay(YourDate) <> vbSaturday AND WeekDay(YourDate) <> vbSunday
IsFifthBusDay = True
Else
IsFifthBusDay = False
End If
End Function
0

Author Comment

ID: 16323568

Thanks,
Will try it.

0

LVL 14

Expert Comment

ID: 16323571
Still not sure what to do about holidays.  Perhaps we could create a table, then have the user enter holidays at the beginning of the year.  (Unless these are available somewhere.)  We'd need to figure out what to do from there, though.
0

Author Comment

ID: 16323629

That is good,
I will worry about the holidays later. Will just test that functionality.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month15 days, left to enroll