I am trying calculate the previous business day from today. I am
trying to use the code below i found on this site, but keep getting
error message when i try and compile, does not recognize rst.FindFirst.
I am using Access 2002:
Function funAddBusinessDay(datStart As Date, intDayAdd As Integer)
'Adds the proper Business day skipping holidays and weekends
'Arvin Meyer 05/26/98 with modifications by G. J. Shears 6/16/98
'Need to add holidays table using Hdate as field name of holidays
On Error GoTo Err_Hand2
Dim rst As Recordset
Dim db As Database
Dim strSQL As String
Dim h_test As Date
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [hDate] FROM tblHolidays ORDER BY
[hdate] ", dbOpenSnapshot)
rst.FindFirst "[hDate] > #" & datStart & "#"
If rst.NoMatch Then
h_test = 0
Else
h_test = rst!hdate
End If
Do While intDayAdd > 0
datStart = datStart + 1
If WeekDay(datStart) <> vbSunday And WeekDay(datStart) <>
vbSaturday
Then
If h_test <> datStart Then
intDayAdd = intDayAdd - 1
End If
End If
If h_test = datStart Then
rst.FindFirst "[hDate] > #" & datStart & "#"
If rst.NoMatch Then
h_test = 0
Else
h_test = rst!hdate
End If
End If
Loop
funAddBusinessDay = datStart
Bye_Now2:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function
Err_Hand2:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
Resume Bye_Now2
End Function
pietlinden@hotmail.com - 12 Feb 2005 04:14 GMT
Simple answer. the last version of Access that used DAO by default was
Access97. Access 2000 and later use ADO by default. And no, there's
no way to override that. (I asked a long time ago, and MichKa said no,
get used to it.) You need to explicitly declare the recordset object to
be a DAO recordset, and either use late binding or register the DAO 3.6
library. Then it'll work like a champ.
So instead of:
Dim db as database
dim rs as recordset
you need to do
dim db as database 'doesn't matter - the Database object only exists in
DAO
dim rs as DAO.Recordset 'makes a HUGE difference- otherwise you get an
ADO recordset.
Rick Brandt - 12 Feb 2005 13:13 GMT
> Simple answer. the last version of Access that used DAO by default
> was Access97. Access 2000 and later use ADO by default. And no,
[quoted text clipped - 14 lines]
> dim rs as DAO.Recordset 'makes a HUGE difference- otherwise you get
> an ADO recordset.
Or you can simply remove the ADO reference at the same time you add the one for
DAO.

Signature
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com