Undefined Function Error

[Aran10]

Option Compare Database

Public Function DateDiffW(BegDate As Date, EndDate As Date) As Integer
'Returns number of days, excluding Saturday and Sunday
'As written, counts both BegDate and EndDate, e.g.,
'Monday - Friday would count as 5 days
'coded by: raskew

Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

This is my function

'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'

This is my query

Fine: IIF(DateDiffw([Due Date],[Return_Date])<=14,Datediffw([DueDate],[Return_Date])*0.2,Datediffw([Due Date],[Return_Date])*0.2+1)

I have saved the vba as 'DateDiffW' but when running the query, i get the error, undefined function datediffw in expression, any ideas for help?

 

[Aran10]

what references do i need selected for the vba to work?

 

[TheAceMan1]

How are ya Aran10 . . .

Move the code to a [blue]standard module[/blue] (a module in the modules window).

Note: your function doesn't calculate properly. Examples

datediffw(#3/6/2011#,#3/13/2011#) = 4 should be 5
datediffw(#3/1/2011#,#3/27/2011#) = 18 sholld be 23

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

Also make sure the name of the module is not the name of the function. I agree with TheAceMan1 that the logic is flawed. There are lots of reliable workday functions on the web and probably in this site's FAQs.

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

here is an easy one:

Public Function CalcWorkdays(StartDate, EndDate) As Integer
    Dim LTotalDays As Integer
    Dim LSaturdays As Integer
    Dim LSundays As Integer
    On Error GoTo Err_Execute
    CalcWorkdays = 0
    If IsDate(StartDate) And IsDate(EndDate) Then
        If EndDate <= StartDate Then
            CalcWorkdays = 0
        Else
            LTotalDays = DateDiff("d", StartDate - 1, EndDate)
            LSaturdays = DateDiff("ww", StartDate - 1, EndDate, 7)
            LSundays = DateDiff("ww", StartDate - 1, EndDate, 1)
            'Workdays is the elapsed days excluding Saturdays and Sundays
            CalcWorkdays = LTotalDays - LSaturdays - LSundays
        End If
    End If
    Exit Function
Err_Execute:
    'If error occurs, return 0
    CalcWorkdays = 0
End Function

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[TheAceMan1]

Howdy MazeWorX . . .

Your calculations for Saturday & Sunday are inaccurate. This is true for any StartDate beginning on a weekday. Remember, were taking a difference and StartDate in not inclusive. You also include [blue]StartDate - 1[/blue] in your parameters which can throw the count as well.

[blue]LSaturdays = DateDiff("ww", #4/3/2011# - 1, #4/30/2011#, 7)[/blue]

... returns 4. It should be 5, inclusive. Correcting accuracy we have:

[blue]         LSaturdays = DateDiff("ww", StartDate, EndDate, 7) - _
                               Int(Weekday(EndDate = 7))
         LSundays = DateDiff("ww", StartDate, EndDate, 1) - _
                             Int(Weekday(EndDate) = 1)[/blue]

[blue]Your Thoughts? . . .[/blue]



See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

hmm interesting ... i agree but not completely.. give me another day testing busy with other stuff but i want to give it another look

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[TheAceMan1]

MazeWorX . . .

Its not that big of a deal. Its a matter of wether or not the bracketing dates are inclusive.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

Actually 4/3/2011 thru 4/30/2011 there are only 4 saturdays
After another look ill stick with what i have For me im looking for workdays inclusive of the dates selected.


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work