Results 1 to 13 of 13
  1. #1
    Battlescar is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    18

    Date difference

    From the Attachment Timelines Table



    Ok I am new at this. On the collection start date such as 04/01/2017(Date) for the business time Line under Development. I need the Kick off date to be 12 months earlier such as 04/01/2016(-12) from the Collection start date. The Design Start date needs to be 11 months earlier from the collection start date. The QDRC testing Date has to be 8 months earlier. The UAT Start date has to be 5 months earlier from the collection start date.
    All this if the if the Status field = "D" then I would have to do the same Status field E and them M and then MM

    started. This will be on a form. So when I change the Collection start date it will automatically change the other fields to the new dates.

    I should be able to do the rest once I figure how to do this one. Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Perhaps you should explore the DateDiff() and DateAdd() functions. Do some research, make an attempt, then if you still have issue, provide details of your attempt.

    I don't understand the data structure nor why you provide an example spreadsheet filled with negative numbers.
    Last edited by June7; 11-24-2017 at 07:56 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If I understand correctly, you want to enter a Collection Start Date and have the other dates calculated:

    Business
    D Development E Enhancement
    Kick Off Date 4/1/2016 =(CollectionStartDate)-12 9/1/2016 =(CollectionStartDate)-7
    Design Start 5/1/2016 =(CollectionStartDate)-11 10/1/2016 =(CollectionStartDate)-6
    QDRC Testing Date 8/1/2016 =(CollectionStartDate)-8 NA NA
    UAT Start 11/1/2016 =(CollectionStartDate)-5 12/1/2016 =(CollectionStartDate)-4
    Collection Start Date 4/1/2017 4/1/2017


    I would use the DateAdd() function.

    If you create a look up table "tblTimeLineMonths", you would end up with 80 records.
    Table structure: (maybe)
    SurveyList, Text ("90 Days Business", "90 Days Social", "Business", "IBSP, Social")
    SurveyPart Text ("Kick Off Date", "Design Start", "QDRC Testing Date", "UAT Start")
    Status, Text ("D", "E", "M", "MM")
    MthOffset, Number - Integer (-12, -11, -8, -5, etc)



    The syntax of the DateAdd function is
    Code:
    DateAdd(interval, number, date)
    . To get the number you would have to use the DLookup function (or write a UDF) to get the interval/offset.

    The syntax for DLookup is
    Code:
    DLookup(Expr, Domain, Criteria)
    Putting them together, you get:
    Code:
    DateAdd("m", DLookup("MthOffset", "tblTimeLineMonths", "[SurveyList] = 'Business' AND [SurveyPart] = 'Kick Off Date'  AND [Status] = 'D' ") , CollectionStartDate)


    The question is "How are you going to use these dates?" Enter into a table? In a query? In an unbound form?

  4. #4
    Battlescar is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    18
    Thanks ssanfu. I will be using a query for a unbound form. I will attach the database tomorrow. It seems to work. I used the the dateadd function () . Should I make a table for the time lines?

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Should I make a table for the time lines?
    Depends.... what are you going to do with the dates?

  6. #6
    Battlescar is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    18
    Hi I have attached the database. If you open frmEQsurveys there is a Subform where the date fields are located. By selecting the Collectiondate field and imputing a date the other date fields should change to reflect the timetable chart. I have attempted to code this field on the on Current event in the collectionStart field. I am not sure if it is working properly. The other field needed for the timetable are from the tblsurveylist field FundingType such as 90 days Business, 90 Days Social, Business, IBSP and Social. If you need more info please let me know. Thanks

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am confused!!

    When I open form "frmEQSurveys", the Survey Name is "2016 Census Program Dissemination Consultation 2016".
    The FiscalYear in the sub form is FY14/15 and the collection date is "2/1/2019".

    Shouldn't the collection date be in 2014 or 2015? If seems to me that the collection date should be in the fiscal year.

  8. #8
    Battlescar is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    18
    Sorry I was playing around with collection dates so some of the records do not reflect the survey year..
    Usually the FY1415 year should reflect dates between "April 2014 to march 2015"
    FY1516 year should reflect dates between "April 2015 to March 2016"
    etc.
    The main problem is with the changing of the Collection date to update the changes from the timetable chart.

    Thanks ssanfu

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Usually the FY1415 year should reflect dates between "April 2014 to march 2015"
    FY1516 year should reflect dates between "April 2015 to March 2016"
    So you could have a Collection start date of 4/1/2014 and all the other dates (Kickoffdate, Design start,...) are earlier than that date?


    I modified the form "frmEQsurveys". I put a note (in red) - the link for that note is Autonumbers--What they are NOT and What They Are
    See "What they are NOT", #6

    I renamed your sub form and created a new one named "sflSurveylistSub" that I modified. See the form module code.
    In the sub form code module I added code so that if 3 fields are null, that record is deleted.
    I did not add code to check if the collection start date is within the Fiscal year dates.


    I'm sure I mentioned this once, but here it is again:
    EVERY module should these two lines as the top two lines of code:
    Code:
    Option Compare Database
    Option Explicit
    Maybe it is close?????
    Attached Files Attached Files

  10. #10
    Battlescar is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    18
    Hi
    Well I changed the Collection date on the subform and nothing happened, Is there something I'm missing? The Kickoff date did not change as well as the design start date QDRC date and the UAT date as per the table???

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, I shouldn't do late night coding.

    Try this.....
    Attached Files Attached Files

  12. #12
    Battlescar is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    18
    Thanks ssanfu. That did the trick. So grateful.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Excellent........

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

Similar Threads

  1. Replies: 3
    Last Post: 03-02-2016, 09:39 AM
  2. Difference between the value from two different date
    By mahmud1180 in forum Programming
    Replies: 2
    Last Post: 10-02-2014, 08:40 AM
  3. Date Difference Not Quite Right
    By StevenCV in forum Access
    Replies: 11
    Last Post: 01-30-2014, 08:12 AM
  4. Date difference
    By Kaaivin in forum Queries
    Replies: 2
    Last Post: 07-03-2013, 06:06 AM
  5. Replies: 1
    Last Post: 02-12-2013, 03:48 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