Problem with module from FAQ

[KevinNewtoAccess]

I am trying to calculate the number of working days (excluding weekend & holiday) between 2 dates. I have found this topic covered in the FAQ section but am having trouble with the code listed there.

I have copied & pasted the following code into a new module

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between the given dates

Dim dbs As Database
Dim rstHolidays As Recordset

Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

NumSgn = Chr(35)

MyDate = Format(StartDate, "Short Date&quot

For Idx = CLng(StartDate) To CLng(EndDate)
Select Case (WeekDay(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
strCriteria = "[HoliDate] = " & NumSgn & MyDate & NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1
Else
'Do Nothing, it is NOT a Workday
End If

End Select

MyDate = DateAdd("d", 1, MyDate)

Next Idx

DeltaDays = NumDays

End Function

When I try to use the Deltadays function I get the following visual basic error

"User defined type not defined" with a highlight on the line
Dim dbs as Database

I am sure this is simply down to my lack of understanding of visual basic!

Any suggestions would be much appreciated!

Thanks
Kevin

 

[PaulF]

you are probably using Access 2000, you need to change
As Database to
As DAO.Database

As Recordset to
As DAO.Recordset

You also need to ensure the the Microsoft DAO Object Library is "checked" in your References. Open any Module in Design View, Click on the Tools Menu, then on References.


PaulF

 

[KevinNewtoAccess]

Paul,

Thanks for the help - this has moved the problem on stage. The module now compiles OK and lists Deltadays as a user defined function in the Access expression builder.

However, when I try to use the function I am getting the Access error "Ambiguous name in query expression 'Deltadays([tblInterval]![start],[tblInterval]![end])'

I have double checked that the table names are as listed in the code :

tblHolidays with fields [Holidate] & [Holiname]

and am looking to use the function to update a test table :

tblInterval with fields [start] [end] & [interval]

Am I doing something stupid???

Thanks
Kevin

 

[MichaelRed]

The Ambigious name in query expression 'DeltaDays ... refers to a NAME used in that expression. You either have more than one procedure named "DeltaDays" or one of your table names is spelled incorrectly, or - MOST PROBABLE - you need to assign the results to a field name, as in:

MyWorkDays = DeltaDays(Deltadays([tblInterval]![start],[tblInterval]![end])

within the query body.

'____________________________________________
You should not need the DAO prefixes in Ms. Access '97, however htis doesn't really hurt anything. Probably uses a little bit more time.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[KevinNewtoAccess]

Michael,

Thanks for the assistance - I think the problem was that I had a duplicate unsaved visual basic module which I was not aware of - this would have caused the duplicate occurrence of the name.

Unfortunately this still has not solved all of the problems!

I am calling the function from a select query with the expression Days: Deltadays([start],[end]) in the field box.

This is attempting to run but is then generating a runtime error "Type mismatch" and highlighting the line
Set dbs = Currentdb

I am using Access 2000 - Is this another case of the code being written for Access 97?

Thanks
Kevin

 

[MichaelRed]

Kevin,

Yes. Others have had problem(s) using the routine w/ Ms. Access 2Y. Since I do not use the 2K version, I don't see the problem. Someone else posted the problem and - I think - a soloution which they worked out. If you find the soloution, I would appreciate it if you e-mail me the fix. I will post it in the FAQ.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over