Average of Working Days

[Fibee]

Hi

I have an Access query in which I calculate the number of Working days between two dates. How do I workout the average of this figure? I have tried using Avg as well as dAvg both in the query and also in the footer of a report but I just get errors. Any ideas as to what I am doing wrong? The current query looks something like this:

SELECT TABLE1.DCAPPTYP, onelineaddress([ADDRESS]) AS Location, TABLE1.REFVAL, TABLE1.DATEAPRECV, TABLE1.DCSTAT, TABLE1.DATEAPVAL, TABLE2.DATEPRNT, WorkingDays([DATEAPVAL],[DATEPRNT])-1 AS WorkingDays
FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.KEYVAL = TABLE2.PKEYVAL
WHERE (((TABLE1.DATEAPRECV) Between [Forms]![Main Menu]![From Date] And [Forms]![Main Menu]![Until Date]) AND ((TABLE1.DATEPRNT) Is Not Null))
ORDER BY TABLE1.DATEAPVAL;

Many Thanks

Fi

 

[lameid]

workingdays is not a built in function. My assumption would be that it is returning errors. Posting the function will help track down the problem.

 

[Fibee]

Thanks for the reply - the Working days function was written by an external source. Here it is:

Public Function WorkingDays(FromDate As Date, UntilDate As Date) As Integer
If IsNull(FromDate) Or IsNull(UntilDate) Then
WorkingDays = 0
Exit Function
End If
If UntilDate <= FromDate Then
WorkingDays = 1
Else
WorkingDays = WeekDays(FromDate, UntilDate) - Holidays(FromDate, UntilDate)
End If
End Function

There is also a weekdays function that it calls:


Public Function WeekDays(FromDate As Date, UntilDate As Date) As Integer
Dim CalDays, I As Integer
Dim ThisDate As Date

If IsNull(FromDate) Or IsNull(UntilDate) Then
WeekDays = 0
Exit Function
End If

If [UntilDate] <= [FromDate] Then
WeekDays = 1
Exit Function
End If

CalDays = (UntilDate - FromDate) + 1
ThisDate = FromDate
For I = 1 To CalDays
If Format(ThisDate, "ddd") = "Sat" Or Format(ThisDate, "ddd") = "Sun" Then
CalDays = CalDays - 1
End If
ThisDate = ThisDate + 1
Next
WeekDays = CalDays

End Function

 

[dhookom]

Do you also have the Holidays() function in a public module? Your code checking for null should not be necessary since your function only accepts date data types:
Public Function WorkingDays(FromDate As Date, UntilDate As Date) As Integer
'neither of these arguments can be null
If IsNull(FromDate) Or IsNull(UntilDate) Then
WorkingDays = 0
Exit Function
End If

When you view the query datasheet, is WorkingDays right or left aligned? I also would not alias a field with the name of a function.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lameid]

Expanding on what dhookom wrote, you can allow it to take nulls by changing the first line

Public Function WorkingDays(FromDate As Date, UntilDate As Date) As Integer

Becomes

Public Function WorkingDays(FromDate As Variant, UntilDate As Variant) As Integer

But if you do that you should also verify that it is a date in code.

If this is fixed, your query will return -1 for working days if there is a null value. You could also change your expression from...

WorkingDays([DATEAPVAL],[DATEPRNT])-1 AS WorkingDays

To

IIF(Isnull([DATEAPVAL]) or Isnull([DATEPRNT]),0 ,WorkingDays([DATEAPVAL],[DATEPRNT])-1) AS WorkingDays


That will give you 0 if a date is null. You can change 0 to whatever would work better (1 perhaps).

 

[Fibee]

Hi
Yes there is a holidays function:

Public Function Holidays(FromDate As Date, UntilDate As Date) As Integer
Dim FDate As String
Dim UDate As String

If IsNull(FromDate) Or IsNull(UntilDate) Then
Holidays = 0
Exit Function
End If

FDate = Format(FromDate, "dd/mmm/yyyy")
UDate = Format(UntilDate, "dd/mmm/yyyy")

Set Dbase = CurrentDb

SQLString = "SELECT Count(Table1_CNDATES.NWDATE) As CountofNWDATE FROM Table1_CNDATES WHERE (((Table1_CNDATES.NWDATE) "
SQLString = SQLString & "between #" & FDate & "# and #" & UDate & "#));"

End Function



The working days is right aligned in worksheet view, I will try the changes you suggest to see if that helps.

Thanks

 

[dhookom]

Your Holidays() function will never return anything other than 0.

How/where are you attempting to average or sum the function's return value?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Fibee]

well I have tried using avg from within the query before the workingdays function eg:

avg(Workdays: WorkingDays([DATEAPVAL],[DATEPRNT])-1)


I have also tried it in the foot of a report that uses the working days field. I just get an error saying its too complex.

 

[dhookom]

In the query, you would not add the alias inside the Avg(). It might look like:
Avg(WorkingDays([DATEAPVAL],[DATEPRNT])-1)
If this was too complex, I would get rid of the Holidays() since it isn't doing anything.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MichaelRed]

to see a function which actually DOES return the working the number of WORKING DAYS between two dates, refer to (see / click on ... ) faq181261 of course to use it you would need to also implement and maintain the holidays ...




MichaelRed