Function Modules - Conversions 97 to 2000 & 2002

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

elioch - 22 Jul 2005 16:53 GMT

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

--
eliochPosted from http://www.pcreview.co.uk/ newsgroup acces

Reply to this Message

RoyVidar - 22 Jul 2005 17:00 GMT

elioch wrote in message <vaWdnU7oXJ8zi3zfRVn_vA@giganews.com> :

> I have come across a Function that calculates networking days taking
> into account bank holidays. My problem is that the Function works
[quoted text clipped - 6 lines]
>
> Thanks

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

...

Signature

Roy-Vidar

Reply to this Message

elioch - 22 Jul 2005 18:10 GMT

Thanks for that Roy, I tried it but I still get a "Undefined function..
error

--
eliochPosted from http://www.pcreview.co.uk/ newsgroup acces

Reply to this Message

Brendan Reynolds - 22 Jul 2005 21:07 GMT

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?

Signature

Brendan Reynolds (MVP)

> I have come across a Function that calculates networking days taking
> into account bank holidays. My problem is that the Function works
[quoted text clipped - 6 lines]
>
> Thanks

Reply to this Message

elioch - 22 Jul 2005 23:38 GMT

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

--
eliochPosted from http://www.pcreview.co.uk/ newsgroup acces

Reply to this Message

Brendan Reynolds - 23 Jul 2005 01:25 GMT

Have you copied the code into a standard module in the Access 2000 MDB?

Signature

Brendan Reynolds (MVP)

> Hi Brendan,
> I have copied the text I have used to run the function in Access 97.
[quoted text clipped - 57 lines]
>
> End Function

Reply to this Message

elioch - 23 Jul 2005 07:10 GMT

Yes..

--
eliochPosted from http://www.pcreview.co.uk/ newsgroup acces

Reply to this Message

Brendan Reynolds - 23 Jul 2005 16:20 GMT

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?

Signature

Brendan Reynolds (MVP)

> Yes...

Reply to this Message

John Spencer (MVP) - 23 Jul 2005 17:56 GMT

Pardon me.

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

> 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,
[quoted text clipped - 9 lines]
> > --
> > eliochPosted from http://www.pcreview.co.uk/ newsgroup access

Reply to this Message

Brendan Reynolds - 23 Jul 2005 20:02 GMT

Thanks John. I guess it might be.

Elioch, you could try reading Doug Steele's article 'Access Reference
Problems' at the following URL. It explains how to check for missing or
mis-matched references and what to do if you find one ...

http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Signature

Brendan Reynolds (MVP)

> Pardon me.
>
[quoted text clipped - 17 lines]
>> > --
>> > eliochPosted from http://www.pcreview.co.uk/ newsgroup access

Reply to this Message