Call a function which is located in a different database?

[Poduska]

Is there a way to call a function that is saved in a common database, say in a shared folder?

I have (inherited)a DueDate(NumOfDays) function which I use to add a code determined number of days to a date field by way of a query, sometimes 30,000 lines. This function takes into account holidays, shutdown days etc and then makes sure the due date does not end on the weekend.

I have included the code just for reference to visualize what a PAIN it is to try and maintain this everywhere!

Public Function AddDays(ByRef MaxReleaseDate As Date) As Date

On Error GoTo Err_PlanDates
    
    Dim tmpDate As Date
    Dim num As Integer
    
    num = 0
    tmpDate = CDate(MaxReleaseDate)
    
            Do While num < 30   '30 calendar days
                 tmpDate = DateAdd("y", 1, tmpDate)
                 
                 If (tmpDate = "1/21/2008" Or tmpDate = "3/21/2008" Or _
                   tmpDate = "5/26/2008" Or tmpDate = "1/1/2008" Or _
                   tmpDate = "7/4/2008" Or tmpDate = "9/1/2008" Or _
                   tmpDate = "11/22/2007" Or tmpDate = "11/23/2007" Or _
                   tmpDate = "12/24/2007" Or tmpDate = "12/25/2007" Or _
                   tmpDate = "12/26/2007" Or tmpDate = "12/27/2007" Or _
                   tmpDate = "12/28/2007" Or tmpDate = "12/29/2007" Or _
                   tmpDate = "12/30/2007" Or tmpDate = "12/31/2007") Then
                     
                 Else
                   num = num + 1
                     
                 End If
                 
             Loop
            
      Do While (WeekDay(tmpDate) = 1 Or WeekDay(tmpDate) = 7 Or _
                    tmpDate = "1/21/2008" Or tmpDate = "3/21/2008" Or _
                   tmpDate = "5/26/2008" Or tmpDate = "1/1/2008" Or _
                   tmpDate = "7/4/2008" Or tmpDate = "9/1/2008" Or _
                   tmpDate = "11/22/2007" Or tmpDate = "11/23/2007" Or _
                   tmpDate = "12/24/2007" Or tmpDate = "12/25/2007" Or _
                   tmpDate = "12/26/2007" Or tmpDate = "12/27/2007" Or _
                   tmpDate = "12/28/2007" Or tmpDate = "12/29/2007" Or _
                   tmpDate = "12/30/2007" Or tmpDate = "12/31/2007")
                
                tmpDate = DateAdd("y", 1, tmpDate)
                num = num + 1
        Loop
  
    PlanDates = tmpDate
      
Exit_PlanDates:
    
    Exit Function
    
Err_PlanDates:

    MsgBox Err.Description
    Resume Exit_PlanDates

End Function

This all works fine, however....

The problem is that the function is utilzied company wide in MANY databases and when maintenance is needed I have to hunt down all the occurances and fix the function.

Is there a way to call a function that is saved in a common database, say in a shared folder?

Thank you for any insight.

 

[Remou]

You can add a reference to a database in the same way you add a reference to a library and run code from that database. You may wish to put those dates in a table.

 

[Poduska]

Reference library, that scares me for the same reason I don't us any custom Active X like the calendar contol that all the machines would not have it loaded.

Although your idea of placing the dates in a table in a common database and then LINKING the table has possibilities!!!

I suppose I would have to code some SQL to say something like

 While Not(in(select [dteHoliday] from [tblHolidays]))
     do "add On day"  ...
[code]

Perhaps bring back all the days from the table in a Recordset and loop though that for each line instead of the query. Have to think about this one.

Thanks again Remou!

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Poduska]

Thanks all for input.

The DLookup function worked for me. I usually shy away from this function as I usually have nightmares trying to get it to work but not anymore!

I am posting my resulting code as a reference for anyone else needing help. Many times I have received inspiration from others work so perhaps I can return the favor to someone.

Basically I use a small one field table which has holiday dates and utilize the DLookup function to check during my looping that the date is NOT a holiday before I count it as a valid day to increment my counter (num).

Public Function AddDaystoDate(ByRef NumDaysToAdd As Integer, ByRef StartingDate As Date) As Date
'#################################################################
'Generic Day adding function.
'The determination of the StartingDate and NumDaystoAdd must be
'establilshed by the calling code.
'#################################################################

On Error GoTo Err_AddDaystoDate
    
    Dim TmpDate As Date
    Dim Num As Integer
    
    Num = 0
    TmpDate = StartingDate
            
'#################################################################
'This adds the number of days passed to the function to the
'starting date passed to the function. This checks the tblHoliday Dates
'to ensure that holiday dates are not counted.
'Weekend days are included as valid days to be utilized for the count of days
'#################################################################
            
            Do While Num < NumDaysToAdd
                     TmpDate = DateAdd("y", 1, TmpDate)
                If Not (IsNull(DLookup("[dteholidaydate]", "tblHolidayDates", "dteHolidaydate= #" & TmpDate & "#"))) Then
                    'Skip this day as it is a holiday, try the next day               '
                Else
                   Num = Num + 1
                End If
            Loop
            
'#################################################################
'This ensures the end date does not fall on a weekend or a holiday
'If it does then keep adding ONE day until OK
'#################################################################
                
            Do While (WeekDay(TmpDate) = 1 Or WeekDay(TmpDate) = 7 Or _
                    Not (IsNull(DLookup("[dteholidaydate]", "tblHolidayDates", "dteHolidaydate= #" & TmpDate & "#"))))

                    TmpDate = DateAdd("y", 1, TmpDate)
            Loop
            
'#################################################################
'This assignes the new incremented date back as a the value of the function
'#################################################################
      
      AddDaystoDate = CDate(TmpDate)
      
Exit_AddDaystoDate:
    
    Exit Function
    
Err_AddDaystoDate:

    MsgBox Err.Description
    Resume Exit_AddDaystoDate

End Function

Thanks everyone, let me know if you have a better way or suggestions to make it better!