Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 1 / February 2005

Tip: Looking for answers? Try searching our database.

Previous Business Day

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mitchell - 12 Feb 2005 04:01 GMT
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

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.