Results 1 to 6 of 6
  1. #1
    AJ_25 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2018
    Posts
    12

    Calculate between Dates

    Hi,
    I am trying to get the correct calculation for months between 2 dates. I have some logic already in Access which I know works and I am trying to get the same out put in SQL but it's not quite there and I think it could be linked to the ROUND function which Access logic has by my SQL server logic doesn't, does anyone know what I need to apply to my SQL logic to match that in Access please? I also know that if the output is a 0, then I need my SQL to change that to a 1.
    Access Logic
    Months: IIf(Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0)=0,1,Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0))
    SQL Server Logic


    DATEDIFF(D,FINANCIALS_LIFE_TO_DATE.DATE_IN, FINANCIALS_LIFE_TO_DATE.DATE_REQUIRED) / (365/12) CONTRACT_LENGTH,

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Without trying to fathom the logic here, what are you trying to do in plain English.

    e.g. something like "I want to capture all things between date A and date B where A = The first of the month a year ago and B = The last day of last month"
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    AJ_25 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2018
    Posts
    12
    Sorry, it's difficult for me to say being very new to Access and SQL server.

    But essential I want to count the number of months between two dates and if the number of months = 0 then change that to a 1 else return the numbers as per the calculation

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Ok - So something like

    Code:
    Case WHEN DateDiff(Month,[Date_Required],[Date_In]) > 0 THEN DateDiff(Month,[Date_Required],[Date_In] ELSE 1 END As Months
    Should get you on the right track.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    AJ_25
    I think I understand what you want to do, but I also think it may not be a real arithmetic calculation.
    For example, 365/12 assumes 12 months with equal number of days. That just doesn't exist.
    You could do difference in days, hours, minutes or seconds, but for Months you may want to make some approximation. (as Minty is showing).

    It may help if you could tell us more about the application.

    What would 2.35 months really mean for example?

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, I think Minty gave a 100% perfect SQL syntax. If you want the correct # of months use the datediffer function instead of calculations

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

Similar Threads

  1. How To Calculate Days Between Dates
    By DigitalAdrenaline in forum Access
    Replies: 3
    Last Post: 08-22-2016, 10:08 PM
  2. Calculate Dates
    By dave_282 in forum Access
    Replies: 5
    Last Post: 01-14-2016, 09:46 AM
  3. Database that calculate Due Dates
    By wipidu in forum Access
    Replies: 5
    Last Post: 07-20-2014, 02:27 AM
  4. Calculate days between two dates by VBA
    By hhuuhn12 in forum Programming
    Replies: 16
    Last Post: 12-10-2013, 03:11 PM
  5. Calculate duration between two dates
    By Tommy1005 in forum Queries
    Replies: 3
    Last Post: 07-02-2012, 05: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