Results 1 to 4 of 4
  1. #1
    Enigma is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    2

    Calculating a two date fields! Help please

    Hello everyone,



    So, I have two text boxes with a Data Type of Date/Time. I also have a check box with a Data Type of Yes/No.

    Here is a Gyazo link (picture) as it would not allow me to upload a .png snapshot using Snipping Tool.

    https://gyazo.com/233b4e8e91e217baf40177814d4ad407 (don't worry, the link is perfectly safe.)

    What I would love, would be for the SLA date be 20 working days (Mon-Fri) after whatever the Date Received is and IF the Fast Track is YES then for the SLA date to be 10 working days from whenever the Date Received is. Let me break it down visually to explain it better.

    EXAMPLE SCENARIO

    Now imagine a user entered the following data...

    Date Received: 02/01/2017 (Monday 2nd January 2017) (so the SLA date would be 20 working days from the 2nd which would be 06/02/2017)
    Fast Track: No
    SLA Date: 06/02/2017 (Monday 6th February 2017)

    Let's say the Fast Track was Yes

    Date Received: 02/01/2017 (Monday 2nd January 2017)
    Fast Track: Yes
    SLA Date: 16/02/2017 (Monday 16th January 2017)

    I have no idea how to do this as I'm a beginner with Access. How would we be able to tell access to ignore Saturday's and Sunday's? And for it to be as accurate as possible, I would also need for access to ignore the holiday dates, for example Christmas, national holidays, etc... these dates could not be included. I hope this makes sense, please don't hesitate to ask me to break it down further.

    Thanks for any help in advanced! It's greatly appreciated.
    -Enigma

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Access does not have a Workdays function like (I believe) Excel does so you have to create/find a UDF (User Defined Function) written in vba. In addition, you need to create and maintain a Holidays table (tblHolidays). By maintain, I mean ensure it always has enough holiday dates identified to suit your locale. Fortunately, it has been done many times before. Take a look here and see if you can follow through with the instructions
    https://msdn.microsoft.com/en-us/lib...ffice.12).aspx
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Enigma is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    2
    Micron, thank you for the quick response, I really appreciate it. Problem I have (and I have read over the link you sent me) is that this doesn't really show me how to 'add' a certain number of working days onto the Date Received. Maybe it does, but my lack of knowledge is blinding me from the obvious truth. I understand the concept of creating a Holiday's table but this does seem overall to complex for me. Would anyone be able to actually show me with some code? Appreciate any help.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    guess I overlooked that part. You add date values using the DateAdd function. Here's a good source for most Access functions you can use. DateAdd is pretty straight forward.
    https://www.techonthenet.com/access/functions/

    I've never seen a solution for eliminating non-working dates due to holidays that didn't involve a table. AFAIK, there is no 'code' solution that would not involve such a table, and to keep it simple, the code portion can be limited to the use of simple built in functions. While some holidays are fixed, others float around and some places have holidays where other locales don't. For these reasons, a table is your only option if you want to eliminate them. If you build the table and can't figure out how to add 20 days to the second date control, I'd do what I can to help. If you want to wait and see if someone offers a completely coded solution, that's fine too. I just don't see that happening.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-29-2016, 04:03 AM
  2. Replies: 2
    Last Post: 07-26-2014, 12:05 PM
  3. Replies: 3
    Last Post: 04-24-2013, 10:44 AM
  4. Replies: 0
    Last Post: 03-09-2012, 07:04 AM
  5. calculating time fields
    By donnan33 in forum Programming
    Replies: 5
    Last Post: 01-19-2012, 12:15 PM

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