Undefined function but no MISSING REFERENCES

[wrightyrx7]

Hi all,

Looked online and cannot find anything to correct this.

When the Database is opened by a new user or i send out an updated front-end to current users an error message appears saying: -

-Undefined function 'weekdays' in expression

This is referring to my function that calculates working days which i use in a couple of my queries. I found this function online and it seems to work but i cannot get rid of the error when opening.

Hope someone can help.

Many thanks
Chris

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

 

[mole999]

maybe I'm a tad blind but I'm not seeing
' DateValue returns the date part only.
startDate = DateValue(startDate)
endDate = DateValue(endDate)

from
https://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx

maybe its struggling to initialise without a date ?

 

[wrightyrx7]

maybe I'm a tad blind but I'm not seeing
' DateValue returns the date part only.
startDate = DateValue(startDate)
endDate = DateValue(endDate)

from
https://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx

maybe its struggling to initialise without a date ?

Hi Mole,

Many thanks for your promp reply.

However im not sure what you mean (im sorry), as you can see I have just got the code from the link you provided and it seems to work when I place it in my query. Im just not sure why i get the error.

Thanks again
Chris

 

[mole999]

You defined StartDate as a Date which is fine, but where in the code tells StartDate which date it actually is, if you can trigger the formula then hover over the values, maybe it will show you what is being used, if it returns "" then you don't have a value for the function to manipulate

 

[wrightyrx7]

You defined StartDate as a Date which is fine, but where in the code tells StartDate which date it actually is, if you can trigger the formula then hover over the values, maybe it will show you what is being used, if it returns "" then you don't have a value for the function to manipulate

I stepped through the code when it started running and it is picking up the Start and End date fine :/ i have manually calculated some of the results its given and they all are correct.

SO im confised why its giving me the error message when its working

 

[mole999]

I don't have any other current ideas

 

[wrightyrx7]

I don't have any other current ideas

Ok thanks for trying

 

[james_lankford]

...

... or i send out an updated front-end to current users an error message appears saying: -

-Undefined function 'weekdays' in expression

...

so this function is in a module somewhere

and when you sent out the new front end to the user you're actually 100% positive you included the module in the new front end ?

just because you have doesn't mean they do

 

[wrightyrx7]

so this function is in a module somewhere

and when you sent out the new front end to the user you're actually 100% positive you included the module in the new front end ?

just because you have doesn't mean they do

Hi James,

I think I found the problem. The function runs in a query when the database opens, however the query cannot run until it has data.


On the first occasion when someone opens the database it asks them to "enable content". Once this has been done the database is fine for the user... until i send out a new front-end, they just have to enable content again.


Chris

 

[mole999]

does that mean it didn't have a date to work the function with ?