1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Date Difference results in weekdays only

Discussion in 'MS Access' started by DFUSSELL, Jun 20, 2007.

  1. DFUSSELL

    DFUSSELL New Member

    Joined:
    Apr 11, 2007
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    billing specialist
    Location:
    Atlanta, GA
    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
     
  2. Hank Freid

    Hank Freid New Member

    Joined:
    May 7, 2008
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    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 a moderator: May 7, 2008
  3. Hank Freid

    Hank Freid New Member

    Joined:
    May 7, 2008
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    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 a moderator: May 7, 2008
  4. Anamika1

    Anamika1 New Member

    Joined:
    Dec 17, 2008
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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.
    :)
     

Share This Page