Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MS Access (http://www.go4expert.com/forums/ms-access-forum/)
-   -   Date Difference results in weekdays only (http://www.go4expert.com/forums/date-difference-results-weekdays-t4783/)

DFUSSELL 20Jun2007 22:00

Date Difference results in weekdays only
 
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

Hank Freid 7May2008 16:44

Re: Date Difference results in weekdays only
 
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:

Hank Freid 7May2008 16:59

Re: Date Difference results in weekdays only
 
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:

Anamika1 17Dec2008 13:18

Re: Date Difference results in weekdays only
 
Quote:

Originally Posted by Hank Freid (Post 28290)
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.
:)


All times are GMT +5.5. The time now is 17:01.