How to find number of Fridays in a month or in a particular interval date

[Irshad Alam]

I want to count that how many friday is there in a month
or from a date field to another date field for example :

Form!Text1 = 15-07-2004
Form!Text2 = 14-08-2004

I want to find that how many fridays were there inbetween
the above two date on a seprate field on that same form.

 

[Nikos Yannacopoulos]

Irshad,

This small function in VBA will calculate the number of Fridays between any
two dates:

Function Count_Fridays(StartDate As Date, EndDate As Date)
Dim FCount As Long
Dim tDate As Date

Count_Fridays = 0
tDate = StartDate

If Weekday(StartDate, vbFriday) <> 1 Then
tDate = tDate - Weekday(StartDate, vbSaturday) + 7
End If

Do While tDate <= EndDate
Count_Fridays = Count_Fridays + 1
tDate = tDate + 7
Loop

End Function

Paste the code in a general module, then on your form put the following in
the controlsource of a textbox to display the number of Fridays:

Count_Fridays([Text1], [Text2])

Use a macro or a line of code like:

Me.ControlName.Requery

(change ControlName to the avtual name of the control displaying the number)

in the Before_Update event of both Text1 and Text2 to requery the textbox
every time one of the two dates changes. Do the same with the form's On
Current event, so it is requeried when you scroll through records.

HTH,
Nikos