Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2007
    Posts
    45

    Unanswered: Calculating Days in a report

    I have a report that I am building that I want it to calculate the number of days between [StartDate] and [CompletedDate] that a piece of equipment is out of our inventory. I used the following formula that I got form Access help:

    =DateDiff("d", [StartDate],[CompletedDate]) but this formula is not meeting my needs for several reasons:

    1. If the [StartDate] and [CompletedDate] are the same, then it calcuates it as zero. However, I need it to count it as 1. If the start day is today and end date is tomorrow, I need that to be counted as 2, not 1 day. Otherwise, I need it to count both ends of the dates, not just the numerical difference.

    2. Also, if the completed date is not filled in, it doesn't calculate it at all.

    Here is my conumdrum. Every month, I will run a monthly report putting in the previous months date range for example: 10/01/07 to 10/31/07. I have the query set up to pull all equpiment that has a start date between this date range OR has Is Null for the completed date to pull equipment that has a start date before the date range used as the parameter. Here is my SQL for the query:

    SELECT qryForMonthlyDownedEquipReportRepairs.Closed, qryForMonthlyDownedEquipReportRepairs.EquipID, qryForMonthlyDownedEquipReportRepairs.[Type of Equipment], qryForMonthlyDownedEquipReportRepairs.GSRID, qryForMonthlyDownedEquipReportRepairs.StartDate, qryForMonthlyDownedEquipReportRepairs.CompletedDat e, qryForMonthlyDownedEquipReportRepairs.Problem, qryForMonthlyDownedEquipReportRepairs.Notes, DateDiff("d",[StartDate],[CompletedDate]) AS [Total Days Out]
    FROM qryForMonthlyDownedEquipReportRepairs
    WHERE (((qryForMonthlyDownedEquipReportRepairs.StartDate ) Between [Forms]![frmReports]![StartDate] And [Forms]![frmReports]![EndDate])) OR (((qryForMonthlyDownedEquipReportRepairs.Completed Date) Is Null));

    This is not working as stated in number 1 above, but also, a piece of equipment could have gone out of service let's say September 19 and as of October 31 still is out of service. I need the report to calculate the total days out using the parameter dates, ie: it would return 31 days, not 0, and would not calculate it based on the start date (from September 19).

    Is this even possible?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by jill.jameson
    1. If the [StartDate] and [CompletedDate] are the same, then it calcuates it as zero. However, I need it to count it as 1. If the start day is today and end date is tomorrow, I need that to be counted as 2, not 1 day. Otherwise, I need it to count both ends of the dates, not just the numerical difference.
    So would it be safe to say that you want to add one day to every date difference?
    Quote Originally Posted by jill.jameson
    2. Also, if the completed date is not filled in, it doesn't calculate it at all.
    If the [CompletedDate] is empty, what do you wish to calculate?
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Posts
    45
    Well, I am trying to calculate how many days of a given month, a piece of equipment has been out. Let me give you two examples of why I need it calculated if there is no completed date:

    1. Equip #606 was pulled out of service on 9/19/07 [StartDate]. It is waiting on a part and as of 10/31/07, it still has not arrived, therefore, no [CompletedDate]. When I pull the report using 10/1/07-10/31/07 in the [StartDate], I have set up the query to give me all equipment that have a start date within this range OR have no completed date so I get all equipment that have been out for that month. If #606 has no completed date, it still has been out of service for the month of October for 31 days and I need the report to reflect this.

    2. Equip #605 was pulled out of service on 10/20/07 [StartDate]. However, as of 10/31/07, it has not been repaired and therefore no completed date. I need the report to calcuate that it was out service for 12 days in the of October.

    If need be, I could set up an unbound report to have two subreports:

    1. One subreport would list all the equipment that were pulled out for that month that have been put back into service during that month; therefore they have a [StartDate] and [CompletedDate] - but I still need to know how to get it to calculate the days with counting both end dates (not just the difference) and I am clueless on how to do this.

    2. The second subreport could list all the equipment that have been pulled out and have no completed date, then I would just need to know how to write the formula to calculate how many days out of the date range that the equipment has been out.

    Does that make sense?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELET DateDiff("d", [StartDate],[CompletedDate]) + 1 As [the diff]
    Code:
    WHERE  Nz([CompletedDate], Now()) BETWEEN ...
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •