Calculate business days (I've read Pat's thread)

[DurhamCons]

I have searched the forums for an answer to my question and haven't found one that I'm competent enough to make work.

I would like to display the average sales per day. I have a report that uses a query which prompts for [Start date:] and [End date:]. I'm having a difficult time determining the number of business days between those two dates.

Thanks in advance!

Jason

 

[Jerry Stoner]

This will get you weekdays, does not take holidays into account but I know there is a function on this board that does that also. Search for it and you may find.

Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
End If
End Function

 

[DurhamCons]

This will get you weekdays, does not take holidays into account but I know there is a function on this board that does that also. Search for it and you may find.

Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
End If
End Function

Thanks, I may have found that one. Now the question is... what do I do with it?

 

[Jerry Stoner]

Put the function in a module. Then reference the function in the query

SELECT Table1.StartDate, Table1.EndDate, DateDiffW([StartDate],[EndDate]) AS Expr1
FROM Table1;

 

[DurhamCons]

Thank you very much for taking the time to help me with this. This is the first DB I've created from scratch without any previous education on Access.

I'm a bit confused regarding the expression I put in the query. It seems to me like it assumes my StartDate and EndDate used to generate the query is stored in a table. It seems to me that the above expression only saved the dates temporarily for the report. Is this correct?

TIA

 

[Jerry Stoner]

The Between Start and end date you put in the query is indeed only used for the report. BTW you should rename the Date field in your table. Date is a keyword for access and using it WILL cause problems.

 

[DurhamCons]

Thanks for the tip. I changed that field to JOB_DATE in an effort to prevent any problems.

So, can I still use what you suggested since the date is not saved anywhere? Is it a good idea to save that information in another table? Your help is very much appreciated!

 

[Jerry Stoner]

Yes of course you can. You do not need the date parameters stored anywhere. Simply pass them to the query.

 

[DurhamCons]

How?

 

[DurhamCons]

Put the function in a module. Then reference the function in the query

SELECT Table1.StartDate, Table1.EndDate, DateDiffW([StartDate],[EndDate]) AS Expr1
FROM Table1;

Here are my questions... hopefully this will spark some more involvement from others...

In that function, it appears to me that it is assumed my start date and end date are stored in a table. This is not the case. As you can see from my screenshot above that I used an expression for the query to ask for the start and end dates for the report. When a user inputs the start/end date, what happens to that data? Is it saved in memory, stored in some temporary (hidden) table, etc? If I knew the answer to that, I would help me work through tying those parameters to other expressions.

Also, I have no idea if I'm asking simple questions that your average Joe should know or if I'm asking more advanced questions. My experience with programming Access began with this DB and I'm having a hard time finding answers to what seem to be easy questions. I've asked for things online that might help me but apparently there aren't any, so I'm forced to bug you guys.

 

[Mile-O]

I've done a mixture of UK bank holidays functions and American holidays functions that you can find if you search for them.

The bold words are suggested search terms to be accommpanied with a search on my name.

 

[DurhamCons]

I did this...

And got Error 13: Type Mismatch...

 

[DurhamCons]

I've done a mixture of UK bank holidays functions and American holidays functions that you can find if you search for them.

The bold words are suggested search terms to be accommpanied with a search on my name.

Thanks. I have already searched the forum several times and found plenty code for modules. I just don't know how to make them work with my reports. I'm new to the site but not new to message forums so I know better then to ask a question without searching first.

For this report, we don't need to count holidays but thanks!