Results 1 to 6 of 6
  1. #1
    tj105 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    2

    Date Calculations in Access

    Hello,

    I am building a database and in it I need to do date calculations. Without too much detail I input a date based on the built in calender from the date/time field and based on that date I need to have the following data returned into other individual fields: 6 months from that date, the days until that date, 2 months from the date input, 4 months from the date input, 5 months from the date input, 60 days from the end date, 45 days from the end date, 30 days from the end date, and 15 days from the end date.



    Example: I input "Sep 10, 2015" and I would get "Mar 10, 2016", "181 Days", "Nov 10, 2015", "Jan 10, 2016", "Feb 10, 2016", "Jan 10, 2016", "Jan 25, 2016", "Feb 9, 2016", "Feb 24, 2016".

    I have read through websites regarding date calculations within Access but I cannot find the specifics for the application that I need. Any help would be appreciated.

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    date math is easy
    2 months: dateAdd("m",2,[myDate])

    60 days: dateAdd("d",60,[myDate])

    (just look at the help on DATEADD for all settings)

  3. #3
    tj105 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    2
    So the DATEADD function works however the issue is remaining. The people I am making the DB for have zero knowledge of SQL and for the most part are scared of access and right now everything is in excel, so I have to make this as user friendly as humanly possible.

    The idea is the end user selects a date through the calender icon or inputs a date in the "Start Date" column for a record. From the date that the user selected the rest of the fields need to populate with the calculated information from the various DATEADD functions that I wrote with SQL. Right now when I run the query I need to type in each date for each field and the DATEADD function does the calculation.

    Thanks for the help.
    Last edited by June7; 09-14-2015 at 02:23 PM.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Saving calculated data is usually not advisable. Just do the calculations when needed.

    Saving calculated data requires code.

    Post the SQL statement of the query for analysis.
    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.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Without too much detail I input a date based on the built in calender from the date/time field
    Actually, detail would be nice (and better). Form name, field names, button names.

    populate with the calculated information from the various DATEADD functions that I wrote with SQL.
    Don't understand this.....

    based on that date I need to have the following data returned into other individual fields:
    Easiest is to have the VBA code in the after update event do the calculations and push the values into the correct fields.


    For specific code, post the dB.


    You are using forms and not tables for data entry..... aren't you??

  6. #6
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143
    dates.zip look at this db and see if that is what you are trying to do. open the form Table1

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

Similar Threads

  1. IIF statement in Date calculations
    By wnicole in forum Access
    Replies: 1
    Last Post: 09-18-2013, 02:26 PM
  2. date calculations in a form field
    By Csalge in forum Forms
    Replies: 3
    Last Post: 03-30-2013, 02:54 PM
  3. date/time field calculations
    By donnan33 in forum Queries
    Replies: 4
    Last Post: 01-13-2012, 11:58 AM
  4. Date Calculations
    By Polarbilly in forum Forms
    Replies: 2
    Last Post: 06-01-2011, 12:55 PM
  5. Date calculations
    By NOTLguy in forum Access
    Replies: 10
    Last Post: 10-09-2010, 06:41 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