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

[USTRAGNU1]

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

[PhilOfWalton]

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

[USTRAGNU1]

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

[PhilOfWalton]

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

[USTRAGNU1]

Phil,

I am a Libra...coincidence?

Have a great weekend!

Mike