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
8
Medium Priority
?
411 Views
Last Modified: 2012-06-22

 Hello experts,
 
 In MS Access I need a VBA code to check if today is the 5th business day of the month.
 
 Thanks.
0
Comment
Question by:LenkaL
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 34

Expert Comment

by:flavo
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

by:JohnK813
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

by:flavo
ID: 16323386
ahh. Sure is

What about public holidays?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 6

Expert Comment

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

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/findingnthparticularweekdayinmonth.asp
0
 
LVL 14

Accepted Solution

by:
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

by:LenkaL
ID: 16323568

   Thanks,
   Will try it.
   
   
0
 
LVL 14

Expert Comment

by:JohnK813
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

by:LenkaL
ID: 16323629

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question