Exclude weekend dates from report

[Guest]

I have a report that calculates the difference (in no. of days) between two
dates, but need to figure out a way to exclude weekend dates if they occur
between those dates, without having to sift through hundreds of records and
make the changes manually.
Is there any to write an expression that could do this?
I'm using an unbound text box (calculated field) in the report for this.

 

[LeAnne]

I have a report that calculates the difference (in no. of days) between two
dates, but need to figure out a way to exclude weekend dates if they occur
between those dates, without having to sift through hundreds of records and
make the changes manually.
Is there any to write an expression that could do this?
I'm using an unbound text box (calculated field) in the report for this.

Hi CMD,

One simple approach would be to exclude Saturdays & Sundays. Frex:

WHERE WeekDay([YourDate]) NOT IN(6,7)

This won't help you on weekday holidays, however.

Roger Carlson has a bunch of examples on his website:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=DateStuff.mdb

hth,

LeAnne

 

[Guest]

Thanks for your response, LeAnne.
I went to the webpage you suggested, but could not access the file; I got a
messag saying that the file was corrupted.
Any other ideas?
CMD

I have a report that calculates the difference (in no. of days) between two
dates, but need to figure out a way to exclude weekend dates if they occur
between those dates, without having to sift through hundreds of records and
make the changes manually.
Is there any to write an expression that could do this?
I'm using an unbound text box (calculated field) in the report for this.

Hi CMD,

One simple approach would be to exclude Saturdays & Sundays. Frex:

WHERE WeekDay([YourDate]) NOT IN(6,7)

This won't help you on weekday holidays, however.

Roger Carlson has a bunch of examples on his website:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=DateStuff.mdb

hth,

LeAnne

 

[LeAnne]

Try navigating there from Roger's main page:

http://www.rogersaccesslibrary.com/

Good luck,

LeAnne

Thanks for your response, LeAnne.
I went to the webpage you suggested, but could not access the file; I got a
messag saying that the file was corrupted.
Any other ideas?
CMD

:

I have a report that calculates the difference (in no. of days) between two
dates, but need to figure out a way to exclude weekend dates if they occur
between those dates, without having to sift through hundreds of records and
make the changes manually.
Is there any to write an expression that could do this?
I'm using an unbound text box (calculated field) in the report for this.

Hi CMD,

One simple approach would be to exclude Saturdays & Sundays. Frex:

WHERE WeekDay([YourDate]) NOT IN(6,7)

This won't help you on weekday holidays, however.

Roger Carlson has a bunch of examples on his website:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=DateStuff.mdb

hth,

LeAnne

 

[Randy]

I have a report that calculates the difference (in no. of days) between two
dates, but need to figure out a way to exclude weekend dates if they occur
between those dates, without having to sift through hundreds of records
and
make the changes manually.
Is there any to write an expression that could do this?
I'm using an unbound text box (calculated field) in the report for this.

Hi again CMD. Access does not offer a function that returns the number of
weekdays between two dates. However users can use Access' open-programming
model to accomplish this. I have developed a function called NumWeekdays
(see below). This function is the fastest ever. Just copy the all the code
below this paragraph, and place it in a module. Save the module and then put
the following code (between the quotes) into the Report's textbox as follow:
"=NumWeekdays([Field1], [Field2])". For more information about this function
please visit: http://randyb.home.att.net/docs/NumWeekdays.html
Regards -Randy

'FUNCTION NUMWEEKDAYS (..)
'This function returns the number of weekdays (Mon-Fri) between
'two (2) given dates. The order of the parameters does not matter.
'A Weekday is defined as any of the five (5) days starting Monday
'through Friday. The function excludes Weekends (Saturdays and
'Sundays), but do not exclude Weekdays that are Holidays.
'The function uses the same algorithm as NumWeekdays()

'Function Limits: Based on Win32 data type limits.
'Valid date range from 1/1/100 to 12/31/9999

'Version 1.0.0 2004/11/20
'Developed by Randy Balbuena (randyb @att.net). United States.

'Copyright © 2004 by Randy Balbuena. All rights reserved.

'TERMS AND CONDITIONS OF USE

'You can freely use this code for your own work (personal or
'commercial), including distribution in executable form, only
'under the following conditions: Permission is granted to use
'this code as long as the author and its email is cited as the
'source, the copyright, and this notice are NOT removed. You
'cannot distribute modified versions of the source code. You
'cannot use this code in printed media (Books, Web, etc) without
'the express permission of the author. This code has been
'examined against logic and code defects, however its author,
'Randy Balbuena, provides the code "as is" without warranties of
'any kind. Its author shall not be liable for any damages
'suffered by you or any third party as a result of using this
'code. In no event will the author be liable for any lost
'revenue, profit, or data, or for direct, indirect, special,
'consequential, incidental, or punitive damages. Support is
'available, but with no formal responsibility from its author.
'You may contact the author for questions, permissions of use,
'comments, issues or donations to Randy Balbuena ([email protected]).
'http://randyb.home.att.net/docs/NumWeekdays.html

Public Function NumWeekdays(d1 As Date, d2 As Date) As Long
If (d1 > d2) Then: Dim aux As Date: aux = d1: d1 = d2: d2 = aux
NumWeekdays = ((Fix((Fix(d2 - d1 + 1)) / 7) * 5) + _
IIf(((Fix(d2 - d1 + 1)) Mod 7), IIf(((Weekday(d1) > 1) And _
(Weekday(d2) < 7)), (Weekday(d2) - Weekday(d1)) + _
IIf(((Weekday(d2) - Weekday(d1)) < 0), 6, 1), _
(Weekday(d2) - Weekday(d1))), 0))
End Function

 

[Guest]

Thank you, Randy. This is awesome!
CMD

I have a report that calculates the difference (in no. of days) between two
dates, but need to figure out a way to exclude weekend dates if they occur
between those dates, without having to sift through hundreds of records
and
make the changes manually.
Is there any to write an expression that could do this?
I'm using an unbound text box (calculated field) in the report for this.

Hi again CMD. Access does not offer a function that returns the number of
weekdays between two dates. However users can use Access' open-programming
model to accomplish this. I have developed a function called NumWeekdays
(see below). This function is the fastest ever. Just copy the all the code
below this paragraph, and place it in a module. Save the module and then put
the following code (between the quotes) into the Report's textbox as follow:
"=NumWeekdays([Field1], [Field2])". For more information about this function
please visit: http://randyb.home.att.net/docs/NumWeekdays.html
Regards -Randy

'FUNCTION NUMWEEKDAYS (..)
'This function returns the number of weekdays (Mon-Fri) between
'two (2) given dates. The order of the parameters does not matter.
'A Weekday is defined as any of the five (5) days starting Monday
'through Friday. The function excludes Weekends (Saturdays and
'Sundays), but do not exclude Weekdays that are Holidays.
'The function uses the same algorithm as NumWeekdays()

'Function Limits: Based on Win32 data type limits.
'Valid date range from 1/1/100 to 12/31/9999

'Version 1.0.0 2004/11/20
'Developed by Randy Balbuena (randyb @att.net). United States.

'Copyright © 2004 by Randy Balbuena. All rights reserved.

'TERMS AND CONDITIONS OF USE

'You can freely use this code for your own work (personal or
'commercial), including distribution in executable form, only
'under the following conditions: Permission is granted to use
'this code as long as the author and its email is cited as the
'source, the copyright, and this notice are NOT removed. You
'cannot distribute modified versions of the source code. You
'cannot use this code in printed media (Books, Web, etc) without
'the express permission of the author. This code has been
'examined against logic and code defects, however its author,
'Randy Balbuena, provides the code "as is" without warranties of
'any kind. Its author shall not be liable for any damages
'suffered by you or any third party as a result of using this
'code. In no event will the author be liable for any lost
'revenue, profit, or data, or for direct, indirect, special,
'consequential, incidental, or punitive damages. Support is
'available, but with no formal responsibility from its author.
'You may contact the author for questions, permissions of use,
'comments, issues or donations to Randy Balbuena ([email protected]).
'http://randyb.home.att.net/docs/NumWeekdays.html

Public Function NumWeekdays(d1 As Date, d2 As Date) As Long
If (d1 > d2) Then: Dim aux As Date: aux = d1: d1 = d2: d2 = aux
NumWeekdays = ((Fix((Fix(d2 - d1 + 1)) / 7) * 5) + _
IIf(((Fix(d2 - d1 + 1)) Mod 7), IIf(((Weekday(d1) > 1) And _
(Weekday(d2) < 7)), (Weekday(d2) - Weekday(d1)) + _
IIf(((Weekday(d2) - Weekday(d1)) < 0), 6, 1), _
(Weekday(d2) - Weekday(d1))), 0))
End Function