Results 1 to 14 of 14
  1. #1
    jmuirman1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    84

    Date function calculation

    I need TO calculate: [DOB] + 85 YEARS - NOW() * [ANNUAL PAYMENT]



    LIKE: 4/8/1951 +85 YEARS - 3/18/2017 = 19 (ROUNDED TO WHOLE YEARS) * $1,000.00 = $19,000.00

    i CAN'T QUITE GET THE STRING RIGHT. I THINK I HAVE TO CONVERT [DOB].

    thanks for your help.

    John

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Have you tried using the dateadd function?


    Sent from my iPhone using Tapatalk

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Dateadd("yyyy", 85, [dob] -now()) * [annualpayment]

    Or very similar


    Sent from my iPhone using Tapatalk

  4. #4
    jmuirman1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    84
    this renders an answer of 18880929.0369329. this isn't quite it. the answer should b 18 or rounded to 19. any ideas?
    thanks,

    John

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Use date() rather than now()


    Sent from my iPhone using Tapatalk

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Code:
    Costing: (Year(DateAdd("yyyy",85,[DOB]))-Year(Date()))*[annual Payment]
    This should work

  7. #7
    jmuirman1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    84
    thanks, but still not working. I used: Year(DateAdd("yyyy",85,[date of birth]))-Year(Date())*[annual Payment]. I get an error - "#Name?".

  8. #8
    jmuirman1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    84
    i'm using access 2013 if that matters.

    =DateAdd("yyyy",85,[date of birth])-Now() comes with 6960.0056712963, which divided by 365 comes up with 19.06. is this a formatting issue?

    thanks,

    John

  9. #9
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Date function calculation

    In #7 you need [annual payment]

    The main issues are that dates and times are stored numbers.

    So now() isn't returning a date but a decimal.

    You may have more success using year(your datething) for all your dates. At least then access is only dealing with 2017, 1967 etc

    However I ran my #7 post in a query and it produced the required result.

    I'm assuming your using this in a query?

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps:
    result: Year(DateAdd("yyyy",85,[date of birth]))-Year(Date())*[annual payment]
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    This is the SQL I used in a query

    SELECT (Year(DateAdd("yyyy",85,[DOB]))-Year(Date()))*[Annual Payment] AS Totalcost, Table1.[annual Payment], Table1.[DOB]
    FROM Table1;

    I set up all data in a single table Table1

  12. #12
    jmuirman1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    84
    i'm using a test box to calculate. I'm out of ca[pacity in y Query. I don't understand your comment about using [annual payment in #7 - thought i used that field???

    in the first text box i used: =Year([date of birth])+85-Year(Now()) then another text box: =([text983]*[annual payment]) and I get the correct answer.

    However, i want to learn the proper way to do this. I pasted your SQL and get an error #Name?. I also use [date of birth] rather than [dob].

    Thanks,

    John

  13. #13
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Why worry about changing what works. Just make the txt983 invisible.

    The sql statement will only work in a query.


    This works in a textbox

    =(Year([dateofbirth])+85-Year(Now()))*[Annual Payment]
    Last edited by andy49; 03-19-2017 at 09:11 AM. Reason: additional

  14. #14
    jmuirman1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    84
    Perfect! Thanks. I thought I could use same language in text and query.

    John

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

Similar Threads

  1. Calculation of steady function
    By ANJIBA in forum Queries
    Replies: 14
    Last Post: 05-07-2014, 02:19 PM
  2. Function calculation part
    By azhar2006 in forum Access
    Replies: 4
    Last Post: 03-25-2014, 02:25 PM
  3. Calculation Function In A Report
    By scubagal in forum Reports
    Replies: 1
    Last Post: 12-27-2011, 02:22 PM
  4. Total Calculation, nz function, not working
    By Jojojo in forum Programming
    Replies: 3
    Last Post: 10-10-2011, 02:33 PM
  5. Various Day Calculation Function
    By access_tom in forum Access
    Replies: 6
    Last Post: 10-13-2010, 02:37 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