Count how many (specific) dates between two dates

[ECEK]

I have a tbl_holidays table.
I have two dates [StartDate] and [EndDate]

Is it possible to write a function that looks to see how many holidays that are in the tbl_holiday table that occur between the two dates?

eg
tbl_holidays
25th December 2016
1st January 2016


StartDate: 1st December 2016
EndDate: 1st January 2016

result = 2

 

[jdraw]

This sort of approach should do it(untested)

Select count(holidayDate) from yourHolidaytable
where HolidayDate between StartDate and EndDate

 

[pbaldy]

A DCount() would be one alternative.

 

[ECEK]

Can I do this in a Query?

 

[jdraw]

see dcount()

 

[ECEK]

Sorry to have to explain further.

I have a tbl_holidays table.That has a list of dates

I have a table of data (tbl_holding)that has two dates within it:
[StartDate] and [EndDate]

Is it possible to write a query or function that I can call or write that looks to see how many dates in the tbl_holidays are between the two dates in the tbl_holding ?

eg
tbl_holidays
25th December 2016
1st January 2016


StartDate: 1st December 2016
EndDate: 1st January 2016

result = 2

Hope this makes better sense !!

Thanks guys by the way, appreciate your time.

 

[pbaldy]

You could certainly create a VBA function that accepted the 2 dates as parameters and returned the result. It could use DCount() or open a recordset using SQL similar to what jdraw posted.

 

[ECEK]

Im just not capable of writing such code. I can paste code into a module then call it from a query.
I'm that's about the limit of my skills.

Thanks for your time though. Much appreciated.

 

[pbaldy]

It will remain the limit of your skills until you dive in.

This may help if you decide to try:

http://www.baldyweb.com/Function.htm

 

[jdraw]

I agree with Paul. There are many tutorials and examples. Time to get more hands-on.
Here is info from M$oft

 

[ECEK]

Nooooooo don't leave me alone !!!!!!

OK so I have a query with my tbl_holding data in it that contains my StartDate and EndDate.

27/05/2016 and 06/06/2016

My tbl_holidays table contains two dates in the Holiday field, 01/01/2016 and 30/05/2016

I have written the following:

Expr1: DCount("Holiday","tbl_holidays","Holiday" Between "StartDate" And "EndDate")

Which gets a result however the result is "2" where it should be "1"

I'm so close to this !!!!!

One last go Guys !!!!

 

[ECEK]

Solved

I found this code that solves the problem.
The whole process runs like this.
I have a StartDate and a number of weekdays to add to it to get my EndDate.
By using this code I can calculate how many workingdays AND holidays (in my tblHoliday table) there are between the StartDate and the EndDate.

If My WorkingDays2 function gives me less than the number of days I've initially added then I need to add an extra day (or two).

Kudos to Arvin Meyer Esq. Many thanks and also to posters. Your patience is greatly appreciated.

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

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

 

[Minty]

@ECEK - Whilst glad you have got a resolution to your problem, I suspect all those that have tried to steer you to do a bit of VBA yourself will be disappointed that you didn't persevere when the suggestions in the other threads were offering you 1) A number of possible solutions (There is always more than one way to skin a cat)
2) Some insight and learning, so next time you need something a little out of the ordinary you would have been able to have a bash yourself, maybe with further guidance. And I'm not being patronising, just see so many people come here that expect solutions on a plate and don't get that helping through learning is so much better.

 

[ECEK]

I concur with your comments. I am taking a Lynda VBA course but simply don't have any concept of where to start.

As it turns out the solution I found was merely paper over the cracks. There's a flaw to it.

 

[jdraw]

Further, and in support of Minty's advice, here is an excellent set of ~70 free videos on Access vba programming by Steve Bishop.


Regarding

There's a flaw to it.

hopefully it is saying that design and testing are extremely important -whether you design/develop or find and apply.

Good luck.

 

[Minty]

If it helps any further I had to develop a customer specific turnaround time to produce ETA times on repair turnarounds.
It sounded very involved initially, but after a bit of paper planning it actually wasn't so complicated.
We store a turn around time in working days for the customer on the customer record;
We store a Repair Start Date that is used to calculate the ETA.
When the product is booked in using similar functions to the ones you have been playing with, we calculate the ETA for the work in progress reports. If for any reason the start date is changed (Re_quote) etc. the ETA is automatically recalculated on the fly the next WIP report that is run.