Count the number of Fridays between 2 dates

[KEKeogh]

What I'm attempting is to put an expression in a query that will count the number of Fridays between two specified dates.

Thanks
Kathie

 

[plog]

I think this VB function will do it:

Function FridaysBetween(d1, d2) As Integer
    ' determines number of Fridays between two dates, d1 is earliest date, d2 is later date
Dim ret, daysbetween
daysbetween = DateDiff("d", d1, d2)
    ' get total days between the two dates
ret = Int(daysbetween / 7)
    ' get whole weeks between the two dates
daysbetween = daysbetween - (7 * ret)
    ' get number remaining days between two dates after removing whole weeks
If (((Weekday(d1) Mod 7) + daysbetween) >= 6) Then ret = ret + 1
    ' determines if the remaining days will cause timeframe to include another friday
FridaysBetween = ret
End Function

 

[RuralGuy]

...and here's a solution: http://access.mvps.org/access/datetime/date0011.htm

 

[raskew]

Hi -

Here's another you can try:

Public Function GetWeekDays(pmoyr As String, pDays As String) As Integer
'************************************************
'Purpose:   Compute number of weekdays
'           in the specified mm/yyyy
'           where 1 = Sunday thru 7 = Saturday
'Coded by:  raskew
'Inputs:    1) ? GetWeekdays("02/2008", "2")   'count Mondays
'           2) ? GetWeekdays("07/2008", "246") 'count Mondays, Wednesdays, g

'Output:    1) 4
'           2) 13
'************************************************

Dim dteStart As Date
Dim dteEnd   As Date
Dim intEnd   As Integer
Dim intStart As Integer
Dim i        As Integer

   dteStart = DateValue(pmoyr)
   dteEnd = DateAdd("m", 1, dteStart) - 1

   For i = 1 To Len(pDays)
      intStart = intStart + IIf(WeekDay(dteStart) <= Int(Mid(pDays, i, 1)), 1, 0)
   Next i

   For i = 1 To Len(pDays)
      intEnd = intEnd + IIf(Int(Mid(pDays, i, 1)) <= WeekDay(dteEnd), 1, 0)
   Next i

   GetWeekDays = intStart + Len(pDays) * (DateDiff("ww", dteStart, dteEnd) - 1) + intEnd

End Function

HTH - Bob

 

[KEKeogh]

Thanks for your answers. Of course now I can't remember where I was going with that. Doh!!

One thing is these are all VBA code but I can't seem to get that from the Design view of a query. Am I missing something?

Kathie

 

[RuralGuy]

All of the code supplied would go in a standard module and then you would invoke it from the query.

 

[plog]

No matter which of the functions listed above you use, you need to add the code for that function in a Module. Then in the design view of your query you create a field like this:

Fridays: FunctionName([Parameter1], [Parameter2])

FunctionName is the name of the function in the Module and the [Parameter] fields are the actual dates you want to pass that function to do the calculation on.

 

[KEKeogh]

ooohhhh! I get it now. I never knew what that Module selection used for.

This may solve many of my other issues too!

Kathie