Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Unanswered: Date Calculation

    I need help. I am trying to set up a query that will read the date from one entry then add 5 days to it excluding weekends. I have searched and tried several different things and have had no luck. I would appreciate any help on this. An example of what I am trying to do:

    If todays date (7/23/04) is entered into a form, I want to take that date and add 5 days to it excluding the weekend, so it would return a date of 7/30/04.

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    In a query use this formula:
    [MyDateField]+5

    Are you automatically trying to update a textbox on a form when another textbox has data entered or changed? Not clear if you are worried about a query or a form?

    If you are populating the form with a query that uses the the formula above you may have to requery the record-source of the form.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  3. #3
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    This isn't perfect but it should help. Put this code in a module and you can call it from your form.

    Public Function AddFive(pDate As Date) As Date
    Dim i As Integer
    Dim TempDate As Date
    Dim dow As Integer

    TempDate = pDate

    Do While i < 5
    dow = Weekday(TempDate)

    If dow <> vbSaturday And dow <> vbSunday Then
    i = i + 1
    End If

    TempDate = DateAdd("d", 1, TempDate)
    Loop

    AddFive = TempDate

    End Function

    Canupus


    Quote Originally Posted by MGranlund
    I need help. I am trying to set up a query that will read the date from one entry then add 5 days to it excluding weekends. I have searched and tried several different things and have had no luck. I would appreciate any help on this. An example of what I am trying to do:

    If todays date (7/23/04) is entered into a form, I want to take that date and add 5 days to it excluding the weekend, so it would return a date of 7/30/04.

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by MGranlund
    I need help. I am trying to set up a query that will read the date from one entry then add 5 days to it excluding weekends.
    Oops! I missed that part of the question!

    If I understand it correctly (which I don't think I do);
    - MON + 4 gives FRI so MON + 5 gives MON since we exclude SAT & SUN. Same for any other weekday!
    - What happens if the date itself is a weekend (SAT or SUN), should both return FRI?
    Last edited by Cosmos75; 07-24-04 at 14:18.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  5. #5
    Join Date
    Jul 2004
    Posts
    1

    Smile Add date

    good day,

    that's good question, it is better if you use DATEADD function which
    easier than you create your own function.

    if im not forgotten the function it is look like this

    n=dateadd("d",5,#01/01/04#)

    n result is 01/06/04
    for more information, take a look at the helpfile..

  6. #6
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Another way to do this type of thing and include public holidays or whatever other days is to make a field in your query for Weekday([YourDateFiled])

    Then you do and IIf based field that makes a 0 for Saturday and Sunday and 1 for the other days and that in turn lets you sum that field based on your date selection.

    You can also include other dates such as "25/12*" perhaps even your birthday

    Mike

  7. #7
    Join Date
    Jul 2004
    Posts
    2
    I have tried the DateAdd function and I can add 5 days but I haven't figured out how to exclude weekeds using the DateAdd function.

    What I would like it to do is add 5 days to the date input into a form. If the date that is input is a Monday then the date output would be a Monday also. Same for Tuesday, Wednesday, etc. No dates that are put in would be a weekend date, all dates entered would be weekdays.

  8. #8
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by MGranlund
    What I would like it to do is add 5 days to the date input into a form. If the date that is input is a Monday then the date output would be a Monday also. Same for Tuesday, Wednesday, etc. No dates that are put in would be a weekend date, all dates entered would be weekdays.
    Use DateAdd() ;
    DateAdd("d",7,[MyDateField])
    Quote Originally Posted by MGranlund
    I have tried the DateAdd function and I can add 5 days but I haven't figured out how to exclude weekeds using the DateAdd function.
    You don't need to exclude weekends since you are just really needing to add 7 days to get next week.

    But you will need to ensure somehow that your datafield is not a SAT or a SUN. Maybe an IIF statement that uses the function Weekday().

    Hope this helps!
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •