Date Difference results in weekdays only

DFUSSELL's Avatar
Newbie Member
MS Access query code needed to determine the difference in weekdays between two dates.

Example:

date 1 is 1/1/07 and date 2 is 1/15/07
14 days difference but only 10 that are weekdays

I am using the following:
datediff("d",[date 1],[date 2])

which results in total days not just the weekdays

Thank you for any help.
Dee
0
Hank Freid's Avatar, Join Date: May 2008
Newbie Member
0k, You need to add a date/time field to your table to use as the datestamp. You then add a hidden control on your form bound to that field. In the Before Update event of the form, you enter the code:

Me.txtUpdatedWhen = Now()

Hope this helps,
Scott<>
Microsoft Access MVP 2007

Regards:

Last edited by shabbir; 7May2008 at 18:01.. Reason: Confine links to signature
0
Hank Freid's Avatar, Join Date: May 2008
Newbie Member
0k, You need to add a date/time field to your table to use as the datestamp. You then add a hidden control on your form bound to that field. In the Before Update event of the form, you enter the code:

select count(*) from
(select to_char(sysdate-1, 'DAY') DAY, a.* from dual a)
where NOT DAY in ('SATURDAY ','SUNDAY ')
and date_field between ...

just replace 'sysdate-1' and 'dual' in line2 and edit the 'between ...' to useful values.
Regards:

Last edited by shabbir; 7May2008 at 18:01.. Reason: Confine links to signature
0
Anamika1's Avatar, Join Date: Dec 2008
Newbie Member
Quote:
Originally Posted by Hank Freid View Post
0k, You need to add a date/time field to your table to use as the datestamp. You then add a hidden control on your form bound to that field. In the Before Update event of the form, you enter the code:

Me.txtUpdatedWhen = Now()

Hope this helps,
Scott<>
Microsoft Access MVP 2007

Regards:
Hello,
I agree with your post.The given code is absolutely right because i used it and it run nicely. Thanks for your code.

Thanks once again.