Results 1 to 5 of 5
  1. #1
    timcstl is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Posts
    3

    date calculation


    sorry been a while since being in access (30 years dba) so trying in query to show calculated result of a date + number of months.
    so i have a pruchasedate which is a date field, then their is a number of months. Need to calculate a enddate being for example (purchasedate + 48)
    so 10/21/2020 + 48 months

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Create a calculated field in the query with something like:

    Code:
    NewDate:DateAdd("m",48,[pruchasedate])
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    timcstl is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Posts
    3
    so this worked at query level but errors if I try this as a calculated column at table with
    DateAdd("m",[numberofPrepaidMonthsinPurchase],[datepurchased])

    is datedadd not allowed as calc in a table?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There is a very limited set of expressions you can use to calculate a field in a table. Note that I said "can". I feel safe in saying that the vast majority of experienced developers do not use calculated fields. You would be far better off having a date field and a frequency for something so that if you alter either value, the query for the form will always present the correct value. Otherwise, change one and you have to recalc for all existing records, which might prove very difficult to do reliably, if at all.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    timcstl is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Posts
    3
    agreed they could alter the number of months fields will leave as is

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

Similar Threads

  1. Date Calculation
    By PTR in forum Access
    Replies: 15
    Last Post: 05-08-2017, 09:58 AM
  2. Replies: 1
    Last Post: 02-10-2017, 03:10 PM
  3. Date Calculation
    By mrkaye in forum Forms
    Replies: 5
    Last Post: 11-11-2010, 01:10 AM
  4. Date Calculation ?
    By techexpressinc in forum Queries
    Replies: 2
    Last Post: 06-24-2009, 09:02 PM
  5. Date Calculation -- HELP!
    By klaauser in forum Forms
    Replies: 0
    Last Post: 12-22-2008, 02:14 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