datediff less the weekends

[ddtpmyra]

I have below query inside the ms access, but this query counts all the days including the weekend but I only wanted to count the NUMBER of weekday (Monday to Friday). Please help

Number of Days Open: DateDiff('d',[dbo_Ticket]![date_opened],Date())

I tried to us the 'w' and 'ww' but it counts the number week not the number of days in a week.

Number of Days Open: DateDiff('w',[dbo_Ticket]![date_opened],Date())

Thanks!

[youmike]

You will probably best meet this need by writing VBA code to test the day of the week for each date between the startdate andthe enddate and incrementing a counter if the day of the week is between Monday and Friday.

You also could do this in SQL using a "WHERE" clause and the COUNT option:

SELECT Count(tblDiary.DiaryDate) AS CountOfDiaryDate
FROM tblDiary
WHERE (((DatePart("w",[DiaryDate])) Between 1 And 5));

The benefit of the VBA alternative is that you could adjust it to exclude holidays

[Panado]

Here is a function that sould do the job.

Private Function DiffJour(ByVal DateDeb As Date, ByVal DateFin As Date) As Integer
 
' Calculates number of days betwwen two dates excluding saturdays and sundays
 
' DiffJour = (DateFin - DateDeb) - (saturdays + sundays).
 
    On Error GoTo Err_DiffJour
 
    Dim DiffJr As Integer
 
' Base difference
    DiffJr = DateDiff("d", DateDeb, DateFin)
 
' Eliminate saturdays and sundays
    Do While DateDeb <= DateFin
        If Weekday(DateDeb, 7) <= 2 Then
            DiffJr = DiffJr - 1
        End If
        DateDeb = DateDeb + 1
    Loop
 
' In case there is no working day between the two dates.
    If DiffJr < 0 Then
        DiffJr = 0
    End If
 
    DiffJour = DiffJr
 
    Exit Function
 
Err_DiffJour:
    MsgBox Err.Description
End Function

[ddtpmyra]

To Panado:

Need more help Im a newbie using the access. My question is... If i'll do this function inside access how can I apply or call this function is it on load of a report?

thanks,
Myra

[NeoPa]

I would suggest using modular arithmetic for the number of whole weeks, then using Weekday() determine which working days are between the two weekdays.