Results 1 to 5 of 5
  1. #1
    Simba1977 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    4

    Calculating Days lapsed in Access Report

    I am new to access but am well versed in Excel. I have tried to search the forums for an answer but do not see a thread for my particular question. I had written a macro in excel to complete a report. However I have since left that position they struggled with the report. Since my replacement is more versed in Access, they want me to convert my report to access. - which seemed easy enough to do. However I am having one big challenge. Calculating the time lapsed in a service.

    Excel is able to identify what today is :=today() then I would subtract today from the date the service occurred, then copy that formula down to all participants.

    How do I convert that calculation to access?

    I have tried to create a calculated field column at the end of the data and tried to create the expression
    Date() - Last Serviced date

    Access keeps erroring out telling me that I can not use the Date() in a calculated field.


    --HELP PLEASE---

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Calculate it in the query,
    dateDiff("d",dateFld, date())

    or
    DateAdd(...)

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Access keeps erroring out telling me that I can not use the Date() in a calculated field.
    Because you can't, calculated fields have limited functionality - as Ranman says, calculate in a query when required

  4. #4
    Simba1977 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    4

    Fix not returning results

    I was able to add the query, but it is not returning any results. I think I uploaded the example file. The example file has been stripped of any identifying information, and limits data to 15 lines. Case Load Example.zip

    Can you take a look and tell me what is still incorrect?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you have it as a criteria - you need to put the formula on the field row

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

Similar Threads

  1. Calculating number of days between dates
    By PATRICKPBME in forum Forms
    Replies: 5
    Last Post: 09-14-2017, 10:44 AM
  2. Replies: 1
    Last Post: 07-02-2014, 08:48 AM
  3. Replies: 1
    Last Post: 03-27-2014, 06:43 PM
  4. Calculating days & Time
    By siapliw in forum Forms
    Replies: 5
    Last Post: 09-11-2012, 11:44 AM
  5. Replies: 4
    Last Post: 09-06-2011, 02:20 PM

Tags for this Thread

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