Declare work_days result as integer 1

[Mulle]

Apologies if this has been asked before, I've searched for ages and couldn't find anything so here goes!

I'm using the function below in a number of queries to calculate date differences in working days.

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Integer
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, &quot;ddd&quot <> &quot;Sun&quot; And Format(DateCnt, &quot;ddd&quot <> &quot;Sat&quot; Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd(&quot;d&quot;, 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays

End Function

This works great to calculate the difference but the problem is that it is returning the result as text rather than a number. This means that when I try to perform calculations on it in a calculated control I get an #Error value! I have tried changing the format in the field properties to general number but it has no effect.
Does anyone know of a way to solve this please? I don't really want to scrap this method and have to build a holidays table.
Any help would be greatly appreciated! Thanks!

 

[PaulBricker]

Well I'm not entirely sure that's the problem but try wrapping each text value in the Val() function. This allows calculations to be preformed on text values.

Paul

 

[JeremyNYC]

What value gets returned as text? Surely not the value of the function, as you've got that declared as an integer.

Why are you using variants? I'd change those to dates and an integer. You'll have to do validation on those values before passing them to this function, but that's probably a better way to go.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[Mulle]

Paul that works a treat! Thanks you so much!

 

[MichaelRed]

unfortunatly, you WILL end up doing the holidays thing ... I have been there numerous times, and the 'average rock' type of supervisor eventually realizes (or is pointedly informed of) the ommission st some point (usually sooner rather than latter).

ergo faq181-261.

The issue with the use of the return value is with the assignemnt of the results. Placed in a Form (TextBox), it is always coerced to String (Text), returned to a variable, it is coerced to the variable type (declaration). You can always coerce it back to (integer) via conversion a function (e.g. CInt([whatever]) will coerce it to &quot;Integer&quot;.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[bustell]

The best way that I have found to calculate true business days including holidays is to create a table that contains two fields: theDate and BusinessDay.

theDate contains every possible date in the range you wish to do calculations on. The BusinessDay field is a boolean field that is 1 if it is a business day and 0 if it is not.

Table Example:

theDate BusinessDay
11/1/02 1
11/2/02 0
11/3/02 0
11/4/02 1
11/5/02 1
11/6/02 1
11/7/02 1

To calculate the number of business days you can do a select statement summing BusinessDays:

SELECT sum(BusinessDay)-1 WHERE theDate BETWEEN startDate and endDate

Example: SELECT sum(BusinessDay)-1 WHERE theDate BETWEEN #11/1/02# and #11/6/02#

The above example will return 3 business days.

The best part of this is that is is table based so you can set up a business days administration form so that administrators can modify which days are considered business days.

Pat B

 

[MichaelRed]

bustell

See FAQ181-261.


Your approach require that ALL dates in the span be entered in the table. I would also not like to maintain the table from period to period without being able to identify the 'name' of the holiday.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[bustell]

MichaelRed,

Yes all the dates are required to be entered into a table which is easily done with a simple loop. You could add another field to the table for a holiday name or reason.

I use this in a SQL Server environment and it provides me the ability to query the business days table as a sub-query to another query to quickly return the number of business days as a field in the master query.

SELECT OrderID, CASE WHEN EndDate Between @Start and @End and StartDate is not null THEN select sum(businessday)-1 from tblBusinessDays where [date] between masterQuery.StartDate and masterQuery.EndDate))ELSE null END AS BusinessDays

FROM view_Orders

It works great! And Pretty quick in Stored Procedures.
Pat B