434,659 Members | 1,939 Online
Need help? Post your question and get tips & solutions from a community of 434,659 IT Pros & Developers. It's quick & easy.

Question regarding posted algorithm for calculating dates that do not fall on a weeke

 P: 36 The post can be found here: https://bytes.com/topic/access/answers/954598-calculate-date-but-do-not-count-weekends-holidays Here is the algorithm by Adezii I am referring to: [code].. 1.Public Function fCalcWorkDays2(dteStartDate As Date, lngNumOfDays As Long) 2. Dim lngCount As Long 3. Dim lngCtr As Long 4. Dim dteDate As Date 5. 6. lngCount = 0 7. lngCtr = 1 8. 9. Debug.Print "Date", "Day Count", "Weekday" 10. 11. Do 12. dteDate = DateAdd("d", lngCtr, dteStartDate) 13. Select Case Weekday(dteDate) 14. Case 7, 1 'Saturday and Sunday, do nothing 15. Case Else 'Monday thru Friday, OK 16. 'Is it a Holiday as posted in tblHolidays? 17. If DCount("*", "tblHolidays", "[Date] = #" & dteDate & "#") < 1 Then 'NOT Holiday 18. lngCount = lngCount + 1 'Increment Counter 19. Debug.Print dteDate, lngCount, Weekday(dteDate) 20. End If 21. End Select 22. lngCtr = lngCtr + 1 23. Loop While lngCount < lngNumOfDays 24. fCalcWorkDays2 = dteDate 25. End Function] [Code/].. I work for the Air Force and I am trying to create function that will do this very thing, but mine is adding too many days. Adezii's function looks much cleaner, but I do not understand a couple of items. 1. Would anyone be able to explain what is meant by line 17: If DCount("*", "tblHolidays", "[Date] = #" & dteDate & "#") < 1 ? Does this mean his dates in tblHolidays are enclosed in pound signs, or is that the way it needs to be written in the function? 2. I don't quite understand how typing Case 7,1 on line 14 tells access to ignore Saturday and Sunday. Can someone explain that? If you have questions or require additional information, please email me. Thanks! Mike Munitz Mar 16 '17 #1
4 Replies

 Expert 100+ P: 1,430 In general, a date needs a hash mark either side of it to tell Access it is a date. So today would be #16 March 2017#. I guess you are American so your dates are even more illogical than English dates. We would write 16/3/2017 and I guess you would write 3/16/2017. Unless there are hash marks which you incorrectly call pounds signs (£ is a pound sign) then access would consider 3/16/2017 as a division sum. (3 divided by 16 divided by 2017) In Answer to your second question, the weekday function returns a number from 1 to 7, 1 being Sunday, 2 Monday etc and 7 = Saturday. So it's saying if Weekday is 1 or 7 (Sunday or Saturday) do whatever follows... but nothing follows, so then we go on to Case Else (Not Sunday or Saturday) do whatever follows, and there we start doing calculations about holidays. Hope that helps. Phil Mar 16 '17 #2

 P: 36 Hi Phil, Yes I am American, and yes our date formats can be batty to deal with, and yes your explanations answer my questions. I too am familiar with the term hash mark. However, I disagree with your overall assessment: pound sign /ˈpound ˌsīn/ noun noun: pound sign; plural noun: pound signs 1. North American - the sign #, representing a pound as a unit of weight or mass, or as represented on a telephone keypad or computer keyboard. 2. the sign £, representing a British pound sterling. Thank you for the quick reply Phil! MikeOfMurrica Mar 16 '17 #3

 Expert 100+ P: 1,430 Ah, Mike, I live & learn. We use Lb. for pounds weight from the Latin Libra - Scales (& also a star constellation and sign of the zodiac) Phil Mar 17 '17 #4

 P: 36 Phil, I am a Libra...coincidence? Have a great weekend! Mike Mar 17 '17 #5