Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73

    Using Dates to count down to a future date.


    Hello, I'm trying to do something with dates that I've not seen before and I don't know how common it is or if I should go about it another way. Here is my situation. I have created a workflow in Access. There are several dates associated with various tasks that need to be completed before the "go live" date. The go live date is always known but the trick is working with the task due dates. Each task due date is counting down to the go live date. For example, a certain task has to be completed 35 day's before the go live date (D-35). Another task must be completed D-29 day's before go live date, etc...the window of time will never change, therefore, as long as I know my go live date I should be able to use this for future work flows as well. I've looked at the DateAdd function but can't find examples of using it to count down. *My go live date and my task dates are in related tables and I've created the form off a query using these two tables. Any suggestions from someone who knows how to do this? Thank you!

  2. #2
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    I was able to use the DateAdd feature to subtract, that part was successful. However, does anyone know to get DateAdd to only count business days, or week days excluding weekends and holidays. All the examples I viewed online seemed to be complex, I was hoping to avoid coding beyond the beginners level.

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    All of the code you need is already written except for a stub in a command button. Why so much reluctance to code?

  4. #4
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    lol, You're right, I do like code though...I just want to make sure I don't fine myself swimming in the middle of the ocean. Do you have sources that I can check for the needed code?

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    To be honest, I can't find the stuff I did years ago.
    Here's a plethora of workday functions: http://access.mvps.org/access/datetime/date0012.htm


  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    BTW, I and many other helpers here would be glad to help you pare down whichever you choose. Just ask.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Want some assistance?

  10. #10
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Thanks, I'm going to view all the links and see which one I think would fit best, I'll let you know, thanks again.

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Okey Dok. Just sing out and we'll be there.

  12. #12
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    I view the links. I like the second one that you sent. I followed the directions to create the working day's module but when I tried using it in a query it said that field was undefined. I'm sure the module was created because I can see it in the module section of the pane, also I opened the module and the coding is there. Maybe I'm not using it correctly in the query. My query contains the field that has the beginning date which was calculated with the DateAdd function and the field that has the go live date. I want the working days feature from the link you sent me to calculate the workdays between those two dates.

  13. #13
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Actually, I didn't tell you correctly, what I'm wanting is a date that does not include weekends. I know of no other way to calculate my due dates other than using the DateAdd function.

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by mbenton View Post
    I view the links. I like the second one that you sent. I followed the directions to create the working day's module but when I tried using it in a query it said that field was undefined. I'm sure the module was created because I can see it in the module section of the pane, also I opened the module and the coding is there. Maybe I'm not using it correctly in the query. My query contains the field that has the beginning date which was calculated with the DateAdd function and the field that has the go live date. I want the working days feature from the link you sent me to calculate the workdays between those two dates.
    The module name can *NOT* be the same as the procedure it contains.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Future Date that may be incremented
    By h1mself2 in forum Access
    Replies: 3
    Last Post: 12-14-2012, 04:11 PM
  2. Replies: 3
    Last Post: 11-20-2012, 01:40 PM
  3. Calculated Future Dates
    By Monterey_Manzer in forum Forms
    Replies: 5
    Last Post: 10-02-2012, 05:02 PM
  4. Future date parameter
    By normie in forum Queries
    Replies: 10
    Last Post: 08-12-2010, 09:38 PM
  5. Count Dates within Date Range
    By eckert1961 in forum Queries
    Replies: 2
    Last Post: 03-08-2009, 10:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums