Results 1 to 7 of 7
  1. #1
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81

    Calculate elapsed days in SQL

    I'd like to build an SQL query that subtracts the received date from today's date. I thought that it should look like this, but this results in an error:

    Code:
    ELAPSED(Days): [INDUCTION_DATA]!Date()-[INDUCTION_DATA]![RECEIVED_DATE]
    How can I calculate elapsed days in an SQL statement?

    Edit:

    Woops, that wasn't SQL it was MS Access Query builder.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    What is 'INDUCTION_DATA]!Date()' ?
    You can't have function as table field!!! Or have you named a field in table this way?

    You can use DATEDIFF(d, StartDateTime, EndDatetime)
    Or simply Int(EndTime - StartTime) - Int() removes any datetime values less tham 24 hours from end result, and forces the result to be an integer instead datetime.
    NB! Time difference can never be negative.

  3. #3
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    I guess I didn't need help on this after all. This seems to work:

    Query Builder:
    Code:
    Elapsed(datediff): DateDiff("d",[INDUCTION_DATA]![REFERENCE_DATE],Date())
    Results in this SQL statement:
    Code:
    SELECT INDUCTION_DATA.*, Date() AS TODAY, DateDiff("d",[INDUCTION_DATA]![REFERENCE_DATE],Date()) AS [Elapsed(datediff)]
    FROM INDUCTION_DATA;
    ...And this gives me what I need.

  4. #4
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    @ArviLaanemets, Yeah, that resulted in '#ERROR'

  5. #5
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    How much complexity can I put into an SQL query? I'd like to calculate radioactivity decay in a query, and this is the cell formula that I use in Excel:

    Code:
    =IF(F44=0,"-",(F44*1000)*(EXP((-(0.693/AB44)*AE44))))
    whereas:
    F44 = activity in millicuries
    AB44 = half life in hours
    AE44 = elapsed time in hours

    I've started building this and Access is giving me a blank column for the part of the equation that I've entered so far....

  6. #6
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    Pretty complex, I guess. I put this in the query builder:

    Code:
    TEMP_NAME: ([INITIAL_ACTIVITY]*1000)*(Exp(-(0.693/[HALF_LIFE HOURS (Hours)])*(DateDiff("h",[INDUCTION_DATA]![RECEIVED_DATE],Date()))))
    Which gave me this SQL:
    Code:
    SELECT INDUCTION_DATA.*, DateDiff("d",[INDUCTION_DATA]![REFERENCE_DATE],Date()) AS [Elapsed(days)], DateDiff("h",[INDUCTION_DATA]![REFERENCE_DATE],Date()) AS [Elapsed(hours)], ([INITIAL_ACTIVITY]*1000)*(Exp(-(0.693/[HALF_LIFE HOURS (Hours)])*(DateDiff("h",[INDUCTION_DATA]![REFERENCE_DATE],Date())))) AS TEMP_NAME
    FROM INDUCTION_DATA;
    And it runs fine

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    In Access you use IIF(Expression,TruePart,FalsePart). To get the elapsed hours use DateDiff("h",[INDUCTION_DATA]![REFERENCE_DATE],Now()) (assumes the reference_date field has a time component).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 13
    Last Post: 02-12-2020, 04:13 PM
  2. Replies: 10
    Last Post: 11-08-2014, 07:28 PM
  3. Replies: 5
    Last Post: 07-01-2014, 02:28 PM
  4. calculate time elapsed
    By chavez_sea in forum Access
    Replies: 3
    Last Post: 07-29-2013, 09:21 PM
  5. Replies: 1
    Last Post: 05-01-2013, 10:53 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