Holiday - Error

J

Jody

I had received a some code years ago that calculated holidays, worked great,
but recently I had to convert the database from 97 to 2000 and know the vb
does not work and since I am not a programmer, I do not understand what is
going on.

This is the error I get:
Compile Error:
User-Defined type not defined

it is highlighting dim rst as dao.recordset.

Below is all of the code

Option Compare Database
Option Explicit

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As 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 between them
' Note that this function has been modified to account for holidays. It
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) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop


Any help anyone can give me is much appreciated. Thanks
 
J

Jody

when I go to tools there is no selection for references, what exactly are you
talking about? Thanks

Drayton T. said:
Make sure that you set the required references.
tools>References>DAO.....

Jody said:
I had received a some code years ago that calculated holidays, worked great,
but recently I had to convert the database from 97 to 2000 and know the vb
does not work and since I am not a programmer, I do not understand what is
going on.

This is the error I get:
Compile Error:
User-Defined type not defined

it is highlighting dim rst as dao.recordset.

Below is all of the code

Option Compare Database
Option Explicit

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As 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 between them
' Note that this function has been modified to account for holidays. It
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) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop


Any help anyone can give me is much appreciated. Thanks
 
J

Jody

Never mind I figured it out, thank you so much!!!!!!!!

Drayton T. said:
Make sure that you set the required references.
tools>References>DAO.....

Jody said:
I had received a some code years ago that calculated holidays, worked great,
but recently I had to convert the database from 97 to 2000 and know the vb
does not work and since I am not a programmer, I do not understand what is
going on.

This is the error I get:
Compile Error:
User-Defined type not defined

it is highlighting dim rst as dao.recordset.

Below is all of the code

Option Compare Database
Option Explicit

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As 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 between them
' Note that this function has been modified to account for holidays. It
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) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop


Any help anyone can give me is much appreciated. Thanks
 
T

Tom van Stiphout

On Thu, 1 Nov 2007 06:39:02 -0700, Jody

Open a code window. Select Tools > References.
Make sure "Microsoft DAO 3.6 Object Library" is selected.
You can uncheck "Microsoft ActiveX DataObjects X.X Library"

-Tom.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top