Access report or Query?

[davefish]

Hi,

I'm new to access and what's it's capable of, but I wonder if anyone can help. In Excel I'm running a formula to ascertain the working days between two dates, eg, excluding weekends. I pull the data from access then use the =NETWORKINGDAYS function to get here. It would be a lot simpler if I can do it all in access, but I cannot find where to place a formulae that works, or in fact which formula to use for that matter.

Can anyone help??

DaveFish

 

[rjoubert]

So you have a custom function (that you coded) called NETWORKINGDAYS? You can put that code in an Access module...click on the Modules tab in Access and create a new one. I'm guessing this would need to be a function, so it will return a number.

Public Function NetWorkingDays(Date1 as Date, Date2 as Date) as Integer
   'calculate the days between date1 and date2, excluding weekends
   NetWorkingDays = YourAnswerGoesHere
End Function

Then save your module; and you will be able to call this from an Access query, or from code behind a form or report.

Good Luck!

 

[davefish]

Hi rjoubert,

The Networkingdays function is an Excel dervied one
=Networkdays(start_date,End_Date,holidays) it's not one I have coded. That being the case does you answer still stand?

DaveFish

 

[rjoubert]

Then you would need to create your own somehow. My answer will get you started, but you would need to write the logic within the function to arrive at your result. I'll do some more noodling on it, and if I come up with something, I'll let you know...

 

[rjoubert]

Try this out...

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

   Dim intCount As Integer

   intCount = 0

   Do While StartDate <= EndDate
      Select Case Weekday(StartDate)
         Case 1, 7
            intCount = intCount
         Case 2, 3, 4, 5, 6
            intCount = intCount + 1
      End Select
   
      StartDate = StartDate + 1
   Loop

   NetWorkingDays = intCount

End Function
[\code]

Note: This doesn't account for holidays.