calculation error

[awake2424]

Trying to create a calculation in a field (text25) that excludes weekends and holidays. I am getting an argument not optional error. Thanks.

 Me.[Text25] = GetBusinessDay([Due_Date - Result_Date])

 

[plog]

The error means the function requires an additional value passed to it. In your code, you are passing 1 value (and probably not the value you think).

You are passing a field called "Due_Date - Result_Date". Do you actually have that field in your data, or are those 2 different fields? If 2 different values you would probably need this call to the function:

GetBusinessDay([DueDate], [Result_Date])

Can you post the code for GetBusinessDay()?

 

[awake2424]

They are two seperate fields, Result_Date is a field and Due_Date is a field. Basically I have another field (Text25) which is a calculation of Due_Date-Result_Date.

So, the code:

 Me.[Text25] = GetBusinessDay([Due_Date] - [Result_Date] was my attempt, but it doesn't calculate a date.

Here is GetBusinessDay: I know this works as I use it in another calculation to exclude weekends and holidays.

 Option Compare Database

Function GetBusinessDay(datStart As Date, intDayAdd As Integer)
On Error GoTo Error_Handler

Dim rst As DAO.Recordset
Dim DB As DAO.Database
'Dim strSQL As String

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHoliday2014", dbOpenSnapshot)

If intDayAdd > 0 Then
    Do While intDayAdd > 0
        datStart = datStart + 1
        rst.FindFirst "[HolidayDate] = #" & datStart & "#"
        If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday Then
            If rst.NoMatch Then intDayAdd = intDayAdd - 1
        End If
    Loop
    
ElseIf intDayAdd < 0 Then

    Do While intDayAdd < 0
        datStart = datStart - 1
        rst.FindFirst "[HolidayDate] = #" & datStart & "#"
        If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday Then
            If rst.NoMatch Then intDayAdd = intDayAdd + 1
        End If
    Loop
    
End If

    GetBusinessDay = datStart

Exit_Here:
    rst.Close
    Set rst = Nothing
    Set DB = Nothing
    Exit Function
    
Error_Handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Here
End Function

Thanks.

 

[namliam]

Function GetBusinessDay(datStart As Date, intDayAdd As Integer)

This function requires 2 parameters to put in and changes a startdate to a new date x workdays, it doesnt calculate the difference in workdays between two dates....

You need another function for that (probably from the same database you found this one in)

 

[namliam]

Ow and I forget, Text25, really?
Default names are to be changed to a proper name, leaving the default names makes for a maintenance nightmare in the future.

 

[awake2424]

I'm not sure I follow. I'm just trying to calculate the difference from the two dates, excluding weekends and holidays.

So, if Due_Date is 9/19/2014 and Result_Date is 9/19/2014, then TAT is 0

if Due_Date is 9/26/2014 and Result_Date is 9/29/2014, then TAT is 1 (the weekend dates are excluded). Thanks .

 Private Sub Result_Date_AfterUpdate()
    Me.[TAT] = GetBusinessDay([Due_Date] - [Result_Date], 0)
End Sub

 

[plog]

Function GetBusinessDay(datStart As Date, intDayAdd As Integer)

This is the function declaration, it tells you what you need to pass it. Each varialbe between the parenthesis is called an argument. Your function takes 2 arguments: the first is a date and the second is an integer.

So, when you pass it [Due_Date] - [Result_Date] as the first argument, it has no idea what to do because that's not a date, its an integer:

1/2/2014 - 1/1/2014 = 1

GetBusinessDays is will not do what you want it to do. You want a function that returns a number which represents the number of work days between two dates. GetBusinessDays returns a new date calculated from a date and an integer.

 

[awake2424]

 Option Compare Database

Public Function fGetWorkdays2(pstart As Date, pend As Date) As Integer
fGetWorkdays2 = 7 - Weekday(pstart) + 5 * (DateDiff("ww", pstart, pend) - 1) + Weekday(pend) - 1
End Function

then I call the function

VB

 Private Sub Result_Date_AfterUpdate()
    Me.[TAT] = fGetWorkdays2([Due_Date] - [Result_Date], 0)
End Sub

Due_Date = 9/26/2014
Result_Date = 9/30/2014
TAT = 5 (should be 2)

and I think only weekends are excluded not the holidays. I have a tblHoliday with all the HolidayDate listed. Thanks .

 

[plog]

First, your code is too complex for a human to understand:

fGetWorkdays2 = 7 - Weekday(pstart) + 5 * (DateDiff("ww", pstart, pend) - 1) + Weekday(pend) - 1

There's no reason to jam everything into one line in a function. Take your time, space things out, document what you are doing so the next guy can understand it if he needs to make changes:

ret=0      
    ' return value, holds weekdays between pstart and pend

ret =7 - Weekday(pstart)
   ' notes about what this line is doing
ret = ret + 5 * (DateDiff("ww", pstart, pend) - 1)
    ' notes about what this line does
ret = ret + Weekday(pend) - 1
    ' notes about what this line did

fGetWorkdays2 = ret

To get the number of holidays between pstart and pend I recommend using DCount(http://www.techonthenet.com/access/functions/domain/dcount.php). Count the number of days in tblHoliday that are in there between pend and pstart. Then subtract that from ret on a new line.

 

[namliam]

If you go look in one of these threads/databases
http://www.access-programmers.co.uk/forums/showthread.php?t=174215&highlight=holidays
http://www.access-programmers.co.uk/forums/showthread.php?t=133329&highlight=holidays
http://www.access-programmers.co.uk/forums/showthread.php?t=238821&highlight=holidays

You should be able to find the proper function to use

 

[awake2424]

 Public Function fGetWorkdays2(pstart As Date, pend As Date) As Integer
ret=0      
    ' return value, holds weekdays between pstart and pend

ret =7 - Weekday(pstart)
   ' notes about what this line is doing [B](defines start as Monday-Sunday)[/B]
ret = ret + 5 * (DateDiff("ww", pstart, pend) - 1)
    ' notes about what this line does [B](Only count Monday - Friday)[/B]
ret = ret + Weekday(pend) - 1
    ' notes about what this line did [B](check to see i date is a sat or sun)[/B]

fGetWorkdays2 = ret
End Function

So for the holidays I am not too familiar with how to use DLookup for this purpose but will try. Thanks .

 

[plog]

Not a Dlookup, a Dcount. DLookup retrieves a specific value in your table--you don't care about that. You want Dcount which counts the number of records in your table meeting the criteria you supply. Check the link I posted about DCount for instructions on using it.

 

[awake2424]

  Public Function fGetWorkdays2(pstart As Date, pend As Date) As Integer
ret=0      
    ' return value, holds weekdays between pstart and pend

ret =7 - Weekday(pstart)
   ' notes about what this line is doing (defines start as Monday-Sunday)
ret = ret + 5 * (DateDiff("ww", pstart, pend) - 1)
    ' notes about what this line does (only count Monday - Friday)
ret = ret + Weekday(pend) - 1
    ' notes about what this line did (check to see if date is a sat or sun)

fGetWorkdays2 = ret
End Function

If the Due_Date is 9/26/2014 and the Result_Date is 9/30/2014
TAT is calculated to 5 with the above (should be 2)

 Dim LTotal as Long
LTotal = DCount("HolidayDate","tblHoliday2014")

pstart = Ret - LTotal

My attempt at using DCount:
tblHoliday2014 has the holidays for the year in a column HolidayDate, so if the date matches pstart then it is excluded (pstart = Ret - LTotal). Thanks .

 

[plog]

A few things wrong:

1. pstart is a date to begin with, you are assigning a numeric value to it--that's allowed in VB but a bad practice--keep your variables the type they start as.

2. You shouldn't change the value of pstart at all. The variable ret is what's going to be returned by the function, so it needs to hold the final result of your calculation.

3. You need to add criteria to your DCount. As it is, Dcount is just counting the total number of records in tblHoliday2014. So its counting Christmas and Easter no matter when pstart and pend are. Look at the documentation for Dcount, the last parameter is how you add criteria. You want to count only records in tblHoliday2014 that fall between pstart and pend.

4. tblHoliday2014 is a poor table to have. What happens in 2015? You shouldn't start another table called tblHolidays2015, you should just dump the holidays into one table and look into it whenever you need. Suppose pstart=12/20/2014 and pend=1/2/2015? You're going to have to look into 1 table to find christmas and another to find new year's. Better if you just have one table for holidays.

 

[awake2424]

Though your code seems much easier, I am not following it. Your points are valid and make some sense and I have made changes accordingly.

If the Due_Date: 9/25/2014 and Result_Date: 9/29/2014 = TAT: 4 (should be 2)
Since 9/27 and 9/28 are weekends only that Thursday 9/25 and Friday 9/26 are used in the calculation. Thank you very much .

VB

 Public Function Weekdays(ByRef startDate As Date, _
    ByRef endDate As Date _
    ) As Integer
    ' Returns the number of weekdays in the period from startDate
    ' to endDate inclusive. Returns -1 if an error occurs.
    ' If your weekend days do not include Saturday and Sunday and
    ' do not total two per week in number, this function will
    ' require modification.
    On Error GoTo Weekdays_Error
    
    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 2
    
    ' The number of days inclusive.
    Dim varDays As Variant
    
    ' The number of weekend days.
    Dim varWeekendDays As Variant
    
    ' Temporary storage for datetime.
    Dim dtmX As Date
    
    ' If the end date is earlier, swap the dates.
    If endDate < startDate Then
        dtmX = startDate
        startDate = endDate
        endDate = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", _
        date1:=startDate, _
        date2:=endDate) + 1
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
        date1:=startDate, _
        date2:=endDate) _
        * ncNumberOfWeekendDays) _
        + IIf(DatePart(Interval:="w", _
        Date:=startDate) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", _
        Date:=endDate) = vbSaturday, 1, 0)
    
    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function

then I call the function:

 Private Sub Result_Date_AfterUpdate()
    Me.[TAT] = Weekdays([Due_Date] - [Result_Date], 0)
End Sub

Also, to exclude holidays, will something like: (have a Holidays table)
ID Day Holiday
1 New Years 1/1/2014
2 New Years 1/1/2015

VB

 ' Count the number of holidays.

Dim nHoliday As Intager

    nHolidays = DCount(Expr:="[Holiday]", _
        Domain:=strHolidays, _
        Criteria:=strWhere)
    
    Weekdays = nWeekdays - nHolidays
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit

 

[plog]

I scanned your code and found these two incompatible lines:

Weekdays(ByRef startDate As Date, _
    ByRef endDate As Date _
    ) As Integer
...
...
...

    Me.[TAT] = Weekdays([Due_Date] - [Result_Date], 0)

Those two things are incompatible. Again, the line you declare the function in tells you what you need to pass to it. You are not passing your function what it requires. Weekdays() wants 2 dates, you are passing it two integers. So your dead before you even start.

My suggestion is to not go for the home run, but hit a few singles (or whatever the cricket metaphorical equivalent is ). Get your code to work in the simpliest manner before throwing a ton of code into it. Get this to work:

 Public Function get_Weekdays(ByRef startDate As Date, ByRef endDate As Date) As Integer
    ret = 0   
    ' return value of function, 0 by default

    get_Weekdays=ret

End Function

That's it, don't add any code, focus on setting it up first before you try and stuff a ton of code in there. Copy that into your module, then set up your call so that it actualy works. Then let's talk about putting your logic in it.

 

[awake2424]

Ok, so I have:

 Public Function get_Weekdays(ByRef startDate As Date, ByRef endDate As Date) As Integer
    ret = 0
    ' return value of function, 0 by default

    get_Weekdays = ret

End Function

then it is called by:

 Private Sub Result_Date_AfterUpdate()
   Me.[TAT] = get_Weekdays([Due_Date] - [Result_Date], 0)
End Sub

TAT = 0. Thanks .

If weekdays is looking for two dates AS Integer should be As Date?

 

[plog]

The 'As Integer' tells you the type of data it is going to return. The two pieces of data inside the parenthesis tells you what type of data you need to pass it.

I believe it is to return an integer. I also believe it requires you submit to it 2 dates. That part is correct, the part you are messing up is not providing it with 2 dates when you call it from Result_Date_AfterUpdate().

 

[awake2424]

 Public Function get_Weekdays(pstart As Date, pend As Date) As Integer

ret=0      
    ' return value, holds weekdays between pstart and pend

ret =7 - Weekday(pstart)
   ' notes about what this line is doing (defines start as Monday-Sunday)
ret = ret + 5 * (DateDiff("ww", pstart, pend) - 1)
    ' notes about what this line does (only count Monday - Friday)
ret = ret + Weekday(pend) - 1
    ' notes about what this line did (check to see if date is a sat or sun)

get_Weekdays = ret

End Function

then called:

 Private Sub Result_Date_AfterUpdate()
             Dim Due_Date As String
             Dim Result_Date As String
Int(NZ(Me.Due_Date))
Int(NZ(Me.Result_Date))

   Me.[TAT] = get_Weekdays([Due_Date] - [Result_Date], 0)
End Sub

I'm not sue if this solution would work, but dates would be passed in the call, I think. Thanks.

 

[plog]

Nope. You are still passing get_Weekdays 2 integers:

Me.[TAT] = get_Weekdays([Due_Date] - [Result_Date], 0)

That's the line to focus on, there was no need to change Result_Date_AfterUpdate()