Results 1 to 14 of 14
  1. #1
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99

    Vba Calculations that returns a Date

    Hey family,
    i am not a programmer, but could someone guide me, is there a way to write a formula that calculates the number of days in a year then returns a date? so lets say 20th May 2020 you got a one year to serve in our calculations we subtract 1 from day; then add number of years to year e.g. Release date will be 19th May 2021

    ONE YEAR{365} or {366} LEAP year must be noted in formula. Time ONE YEAR and under we divide by 2; OVER a year or years we divide by 3. WE LOOK for a flat number here NO remainders e.g. 10/3 =3 flat. NOTE THE ANSWER FROM THIS NUMBER WE then work backwards to return a date????

    e.g. (2) 20th May 2020... Four 4 years Time IN
    Enddate 19th May 2024 here now i need to calculate the AMT of days between 20/5/20 AND 19/5/24 = 1461 Days divide this number by 3 because its more than a year =487 Days note there cannot be remainders


    we take this 487Days number and work backwards from 19th May 2024 to get a DATE ANYONE here can write a code for this ?

  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
    Sounds like you need to use the DateAdd() function. There are countless references on its use e.g. https://www.techonthenet.com/access/...te/dateadd.php

    Edit:
    DateAdd("yyyy",1,#20/05/2020#)-1 will return 19/05/2021

    Take a look. Have a read about it but don't hesitate to post back if you need further help.
    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
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by Bob Fitz View Post
    Sounds like you need to use the DateAdd() function. There are countless references on its use e.g. https://www.techonthenet.com/access/...te/dateadd.php

    Take a look. Have a read about it but don't hesitate to post back if you need further help.
    WOW.... Bob Fitz I love guys seriously.... jus found it out using sql statements in query, want to learn how to write it in vba Language. thinking analytical is great. I cant believe I figure it out, I've learned a lot since joining this forum

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Did you see the edit that I made to my last post:

    DateAdd("yyyy",1,#20/05/2020#)-1 will return 19/05/2021
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by Bob Fitz View Post
    Did you see the edit that I made to my last post:

    DateAdd("yyyy",1,#20/05/2020#)-1 will return 19/05/2021
    YES!.... it works need a series of functions in sql Language; same DateAdd in Weeks Months or Years -1. Then DateDiff another field with new field from [DateAdd answer]then use Round function when dividing by and Last subtract last created Round field from first DateAdd field and Returns date as Answer. Still would love to write in vba to automate this function. I hope you understood the way I tried to explain if not ill try a write the sql language layout

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    How do you want to treat leap year? If date range includes a Feb 29, do you want to subtract a day?

    days between 20/5/20 AND 19/5/24 = 1461

    One of those days is Feb 29, 2024.

    Following can determine if a year is a leap year.

    Month(DateSerial(yr, 2, 29)) = 2

    How do you want to determine if period is more than a year - more than 365 days? How should leap year be considered?

    Something to get you started:
    Code:
    Function GetDate(dteD1 As Date, intYrs As Integer) Dim dteD2 As Date, intDays As Integer dteD2 = DateAdd("yyyy", intYrs, dteD1) intDays = DateDiff("d", dteD1, dteD2) GetDate = DateAdd("d", -1 * Round(intDays / IIf(intDays > 365, 3, 2), 0), dteD2) End Function


    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.

  7. #7
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by June7 View Post
    How do you want to determine if period is more than a year - more than 365 days? How should leap year be considered?
    I don't know I've figured it out in sq but i have to do three (3) queries Weeks, Months and Years. l don't know if there's a built in functionality in software where leap year as dates are concern. However is there a way to code 365 days (3) three times and the fourth 366 like a LOOP or something in terms of functionality?...
    Reason for this is because A given Year could be 365 or 366 and must be noted in equation

  8. #8
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by Jamesy_007 View Post

    ...l don't know if there's a built in functionality in software where leap year as dates are concern...
    Have't had coffee yet, so I'm a little fuzzy about your object, here, but when using Access date functions, such as DateAdd() the Access Gnomes know whether a Leap Year is involved and automatically take it into account...no intervention on the dealer's part is needed.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by Missinglinq View Post
    know whether a Leap Year is involved and automatically take it into account...no intervention on the dealer's part is needed.
    yep well then I've sort of figured it out... Thanks
    Quote Originally Posted by Missinglinq View Post
    Linq ;0)>
    what is this??????

  10. #10
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Hi, notice something Today! is there a way to Round down say an answer is 75.5 but I want it to compute 75 and not 76 Round up; how to Round Down????

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You want 75.6 to round up as normal?

    If you want 75.5 to round down, will need to write your own VBA function.

    That's Missinglinq's signature with emoticon.
    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.

  12. #12
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by June7 View Post
    That's Missinglinq's signature with emoticon.
    or OK lol...

    Quote Originally Posted by June7 View Post
    You want 75.6 to round up as normal?

    That's Missinglinq's signature with emoticon.
    No... if its a flat return Fine.... but where there's a decimal returned want functionality to Round down the number; it throws the Release date off by a Day... Hmmmm

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Maybe just take the Integer part? Int(somenumber)
    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.

  14. #14
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by June7 View Post
    Maybe just take the Integer part? Int(somenumber)
    yep this is the trick...

    Thanks June7 and everyone well I guess this thread is solved well.... at least in sql, will try the vba code you wrote sometime!!!

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

Similar Threads

  1. calculations on date
    By harrie in forum Access
    Replies: 3
    Last Post: 12-06-2016, 10:59 PM
  2. Replies: 2
    Last Post: 06-25-2014, 11:00 AM
  3. Replies: 3
    Last Post: 03-28-2014, 07:27 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