Calculating weekdays in Access

[Guest]

What do I enter in the criteria of a query to calculate number of weekdays
between two date fields?
example: find out how many weekdays are between field [ReceivedDate] and
[Sent days]

 

[Michel Walsh]

Hi,


One of the possibilities is to use some VBA functions presented in
http://www.mvps.org/access/datetime/date0012.htm



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I have never used VBA. Do you know a way to enter the process in design view?

Hi,


One of the possibilities is to use some VBA functions presented in
http://www.mvps.org/access/datetime/date0012.htm



Hoping it may help,
Vanderghast, Access MVP

What do I enter in the criteria of a query to calculate number of weekdays
between two date fields?
example: find out how many weekdays are between field [ReceivedDate] and
[Sent days]

 

[Michel Walsh]

Hi,


Cut and paste them in a standard VBA module (by opposition to a module of a
FORM, REPORT, or CLASS).


You can then use the functions in the query designer (with the right number
of argument each function requires).



Keep the name Module1 for the module, don't use a name already used by one
of the function, when it will be time to "save" the module.



Hoping it may help,
Vanderghast, Access MVP

I have never used VBA. Do you know a way to enter the process in design
view?

Hi,


One of the possibilities is to use some VBA functions presented in
http://www.mvps.org/access/datetime/date0012.htm



Hoping it may help,
Vanderghast, Access MVP

What do I enter in the criteria of a query to calculate number of
weekdays
between two date fields?
example: find out how many weekdays are between field [ReceivedDate]
and
[Sent days]

 

[peregenem]

One of the possibilities is to use some VBA functions

One problem with a VBA solution is that it is not available outside the
Access UI. Also, tweaking such code to accommodate business rules may
be difficult.

For a pure SQL solution, a calendar may be used. Google for it e.g.

http://www.aspfaq.com/show.asp?id=2519

(excuse the SQL Server bias in the article.)

 

[Michel Walsh]

Hi,


True, but if you can use automation, which assumes Access is installed
at the client, you can use an Access Application Object, and this object
will be able to reach its embedded VBA code. If Access is not to be assumed
living at the client side, not trivial but doable, you can make the
automation code inside a DCOM component and force this component to run
where the data resides! That assumes you run in a Windows environment. Now,
if you run under a Linux environment...

Your Eastern Sunday is the Roman, the Orthodox, or the 'Jewish' one ?
(They very rarely fall the same day all three of them... unfortunately, I
lost the reference about this "interesting" fact)



Vanderghast, Access MVP