Function Modules - Conversions 97 to 2000 & 2002

E

elioch

I have come across a Function that calculates networking days takin
into account bank holidays. My problem is that the Function work
perfectly in Access 97 but does not work in either a 2000 or a 200
version. I believe it has to do with ADO & DAO, but I do not understan
enough to follow these arguments.

Is there anybody out there that can help without moving too far awa
from Layman's English?

Thank
 
R

RoyVidar

elioch wrote in message said:
I have come across a Function that calculates networking days taking
into account bank holidays. My problem is that the Function works
perfectly in Access 97 but does not work in either a 2000 or a 2002
version. I believe it has to do with ADO & DAO, but I do not understand
enough to follow these arguments.

Is there anybody out there that can help without moving too far away
from Layman's English?

Thanks
Click to expand...

Wild guess
1 - In VBE - Tools | Referenses, ensure there's a checkmark on the
referense Microsoft DAO 3.6 Object Library
2 - per each declaration of DAO objects, use explicit declaration, i e

dim rs as dao.recordset
dim db as dao.database
dm qd as dao.querydef
dim td as dao.tabledef

....
 
B

Brendan Reynolds

Where did you find the function? If it is online, please post the URL, and
I'll have a look at it. Also, could you describe what happens when you try
to use it in Access 2000 or later?
 
E

elioch

Hi Brendan,
I have copied the text I have used to run the function in Access 97
The error I get when it is run in Access 2000 and above is "Undefine
function 'WorkingDays2' in expression".


Public Function WorkingDays2(StartDate As Date, EndDate As Date) A
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays betwee
them
' Note that this function has been modified to account for holidays. I
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

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

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturda
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Functio
 
B

Brendan Reynolds

I've copied and pasted the code into a standard module in Access 2003, and
it works fine for me. It does require a reference to the DAO object library,
but I understand you've already checked that, and you would be getting a
different error message if that was the problem anyway. How are you calling
the function?
 
J

John Spencer (MVP)

Pardon me.

Could the problem be one of the OTHER references? One marked missing, etc.
 
Top