Counting how many bank holidays fall between dates with result in a form?

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

I have a table which contains all the bank holidays for the year.

I also have a form which has two date entries numStartDate and numEndDate.

I need to be able to count how many Bank Holidays are in between these dates
and show the number in the form in field numBankHolidays

I assume I need a Loop statment but I can't make it work.

The table and field names are tblBank_Holiday_Dates numHolidayDates

Thanks

Les
 
Try something like this in the Control Source of your text box:

=DCount("*", "tblBank_Holiday_Dates",
"[numHolidayDates] Between " &
Format([numStartDate], "\#mm\/dd\/yyyy\#") And " &
Format([numStartDate], "\#mm\/dd\/yyyy\#")
 
Thanks Allen I have tried this and I get a message saying Invalid Date
Value.

My fields are formatted to dd/mm/yy I have tried changing your code to shoe
/yy but still get the same message.

Can you help further please

Les



Allen Browne said:
Try something like this in the Control Source of your text box:

=DCount("*", "tblBank_Holiday_Dates",
"[numHolidayDates] Between " &
Format([numStartDate], "\#mm\/dd\/yyyy\#") And " &
Format([numEndDate], "\#mm\/dd\/yyyy\#")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Les said:
I have a table which contains all the bank holidays for the year.

I also have a form which has two date entries numStartDate and numEndDate.

I need to be able to count how many Bank Holidays are in between these dates
and show the number in the form in field numBankHolidays

I assume I need a Loop statment but I can't make it work.

The table and field names are tblBank_Holiday_Dates numHolidayDates

Thanks

Les
Click to expand...
Click to expand...
 
Hi Les,

There are loads of ways to do this, I would select records from the table
using the to dates as criteria. Something like:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLText As String
strSQLText = "SELECT tblBank_Holiday_Dates.*" & vbCrLf
strSQLText = strSQLText & " FROM tblBank_Holiday_Dates" & vbCrLf
strSQLText = strSQLText & " WHERE
(((tblBank_Holiday_Dates.numHolidayDates) Between #" _
& Format(Me![txtStartDate], "mm/dd/yyyy") & "# And #" _
& Format(Me![txtEndDate], "mm/dd/yyyy") & "#));"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQLText)
If Not rs.EOF Then
rs.MoveLast
Me.txtBankHolidays = rs.RecordCount
Else
Me.txtBankHolidays = 0
End If
rs.Close

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/
 
Mark thanks is this meant to go in the Control Source or on an event.

When I put it in the Contol Source I get the #Name# message. I have checked
all the field and table names and they are all correct . I then tried it in
the On Enter event and it comes up with Expected End at the Where statement.

Can you help further please?


Mark Phillipson said:
Hi Les,

There are loads of ways to do this, I would select records from the table
using the to dates as criteria. Something like:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLText As String
strSQLText = "SELECT tblBank_Holiday_Dates.*" & vbCrLf
strSQLText = strSQLText & " FROM tblBank_Holiday_Dates" & vbCrLf
strSQLText = strSQLText & " WHERE
(((tblBank_Holiday_Dates.numHolidayDates) Between #" _
& Format(Me![txtStartDate], "mm/dd/yyyy") & "# And #" _
& Format(Me![txtEndDate], "mm/dd/yyyy") & "#));"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQLText)
If Not rs.EOF Then
rs.MoveLast
Me.txtBankHolidays = rs.RecordCount
Else
Me.txtBankHolidays = 0
End If
rs.Close

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/


Les said:
I have a table which contains all the bank holidays for the year.

I also have a form which has two date entries numStartDate and numEndDate.

I need to be able to count how many Bank Holidays are in between these dates
and show the number in the form in field numBankHolidays

I assume I need a Loop statment but I can't make it work.

The table and field names are tblBank_Holiday_Dates numHolidayDates

Thanks

Les
Click to expand...
Click to expand...
 
It could go in the After Update Event of the txtEndDate.

When you paste the code it may break a line in the wrong place - you may
have to edit where the lines breaks.

i.e. this should be one line:

strSQLText = strSQLText & " FROM tblBank_Holiday_Dates" &
vbCrLf

--

Cheers
Mark

Les said:
Mark thanks is this meant to go in the Control Source or on an event.

When I put it in the Contol Source I get the #Name# message. I have checked
all the field and table names and they are all correct . I then tried it in
the On Enter event and it comes up with Expected End at the Where statement.

Can you help further please?


Mark Phillipson said:
Hi Les,

There are loads of ways to do this, I would select records from the table
using the to dates as criteria. Something like:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLText As String
strSQLText = "SELECT tblBank_Holiday_Dates.*" & vbCrLf
strSQLText = strSQLText & " FROM tblBank_Holiday_Dates" & vbCrLf
strSQLText = strSQLText & " WHERE
(((tblBank_Holiday_Dates.numHolidayDates) Between #" _
& Format(Me![txtStartDate], "mm/dd/yyyy") & "# And #" _
& Format(Me![txtEndDate], "mm/dd/yyyy") & "#));"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQLText)
If Not rs.EOF Then
rs.MoveLast
Me.txtBankHolidays = rs.RecordCount
Else
Me.txtBankHolidays = 0
End If
rs.Close

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/


Les said:
I have a table which contains all the bank holidays for the year.

I also have a form which has two date entries numStartDate and numEndDate.

I need to be able to count how many Bank Holidays are in between these dates
and show the number in the form in field numBankHolidays

I assume I need a Loop statment but I can't make it work.

The table and field names are tblBank_Holiday_Dates numHolidayDates

Thanks

Les
Click to expand...
Click to expand...
Click to expand...
 
GetBusinessDay is a function I wrote 5 or 6 years ago that adds them. Last
year I modified it so that it will also subtract them as well as add them.
You can get the code here:

http://www.datastrat.com/Code/GetBusinessDay.txt

The following code, written earlier and also modified for an app last year
does exactly what you are asking:

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Modified to allow for holidays May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
'....................................................................
On Error GoTo Err_WorkingDays

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

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks Mark

I played around with it and I found out that a " had somehow been inserted
after WHERE I removed it and it works fine now.

Les

Mark Phillipson said:
It could go in the After Update Event of the txtEndDate.

When you paste the code it may break a line in the wrong place - you may
have to edit where the lines breaks.

i.e. this should be one line:

strSQLText = strSQLText & " FROM tblBank_Holiday_Dates" &
vbCrLf

--

Cheers
Mark

Les said:
Mark thanks is this meant to go in the Control Source or on an event.

When I put it in the Contol Source I get the #Name# message. I have checked
all the field and table names and they are all correct . I then tried
Click to expand...
it
in
the On Enter event and it comes up with Expected End at the Where statement.

Can you help further please?


Mark Phillipson said:
Hi Les,

There are loads of ways to do this, I would select records from the table
using the to dates as criteria. Something like:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLText As String
strSQLText = "SELECT tblBank_Holiday_Dates.*" & vbCrLf
strSQLText = strSQLText & " FROM tblBank_Holiday_Dates" & vbCrLf
strSQLText = strSQLText & " WHERE
(((tblBank_Holiday_Dates.numHolidayDates) Between #" _
& Format(Me![txtStartDate], "mm/dd/yyyy") & "# And #" _
& Format(Me![txtEndDate], "mm/dd/yyyy") & "#));"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQLText)
If Not rs.EOF Then
rs.MoveLast
Me.txtBankHolidays = rs.RecordCount
Else
Me.txtBankHolidays = 0
End If
rs.Close

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/


I have a table which contains all the bank holidays for the year.

I also have a form which has two date entries numStartDate and numEndDate.

I need to be able to count how many Bank Holidays are in between these
dates
and show the number in the form in field numBankHolidays

I assume I need a Loop statment but I can't make it work.

The table and field names are tblBank_Holiday_Dates numHolidayDates

Thanks

Les
Click to expand...
Click to expand...
Click to expand...
 
Thanks Arvin got it working now.

Les


Arvin Meyer said:
GetBusinessDay is a function I wrote 5 or 6 years ago that adds them. Last
year I modified it so that it will also subtract them as well as add them.
You can get the code here:

http://www.datastrat.com/Code/GetBusinessDay.txt

The following code, written earlier and also modified for an app last year
does exactly what you are asking:

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Modified to allow for holidays May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
'....................................................................
On Error GoTo Err_WorkingDays

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

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Les said:
I have a table which contains all the bank holidays for the year.

I also have a form which has two date entries numStartDate and
Click to expand...
numEndDate.
I need to be able to count how many Bank Holidays are in between these dates
and show the number in the form in field numBankHolidays

I assume I need a Loop statment but I can't make it work.

The table and field names are tblBank_Holiday_Dates numHolidayDates

Thanks

Les
Click to expand...
Click to expand...
 
Field Formatting is not germane if the fields are genuinely date/time
fields, because it only controls _display_.

Les said:
Thanks Allen I have tried this and I get a message saying Invalid Date
Value.

My fields are formatted to dd/mm/yy I have tried changing your code to shoe
/yy but still get the same message.

Can you help further please

Les



Allen Browne said:
Try something like this in the Control Source of your text box:

=DCount("*", "tblBank_Holiday_Dates",
"[numHolidayDates] Between " &
Format([numStartDate], "\#mm\/dd\/yyyy\#") And " &
Format([numEndDate], "\#mm\/dd\/yyyy\#")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Les said:
I have a table which contains all the bank holidays for the year.

I also have a form which has two date entries numStartDate and numEndDate.

I need to be able to count how many Bank Holidays are in between these dates
and show the number in the form in field numBankHolidays

I assume I need a Loop statment but I can't make it work.

The table and field names are tblBank_Holiday_Dates numHolidayDates

Thanks

Les
Click to expand...
Click to expand...
Click to expand...
 
Back
Top