Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    37

    Unanswered: Several date formulas in query

    I have a DB which produces several reports.
    Each report has a different formula for determining the number of DAYS

    One uses DateDiff("d",[DATE RECEIVED],Now()) AS DAYS another one uses

    DateDiff("d",[ASSIGNED DATE ],[RC SENT DATE]) AS DAYS and one uses Avg(DateDiff("d",[DATE
    RECEIVED],[DATE OF DECISION])) AS DAYS each of these is different because of the data in the report.

    Then I created a report to summarize all my reports including the average days for each report and I'm using this query to create the report:

    SELECT
    tblAPPLICATIONS.SPECIALIST,
    tblAPPLICATIONS.REPORTS,
    Avg(DateDiff("d",[DATE RECEIVED],[DATE OF DECISION])) AS DAYS,
    dateadd("yyyy",-1,dateadd("m",1,date())-day(dateadd("m",1,date()))+1) & " - " & dateadd("m",1,date())-day(dateadd("m",1,date())) AS [REPORT PERIOD],
    Format(Int([Days]/365),"00-") & Format(Int((([Days] Mod 365)/30)),"00-") & Format(([Days]-(Int([Days]/365)*365+Int(([Days] Mod 365)/30))*30),"00") AS MyDays
    FROM tblAPPLICATIONS
    WHERE (((tblAPPLICATIONS.[DATE OF DECISION])>=DateAdd("yyyy",-1,Date())+1))
    GROUP BY tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.REPORTS;

    The problem is that I'm missing two of my reports when I run my summary report. The two
    reports that are missing use these formulas to calculate the days:

    One uses DateDiff("d",[DATE RECEIVED],Now()) AS DAYS

    DateDiff("d",[ASSIGNED DATE ],[RC SENT DATE]) AS DAYS

    Can someone show me how to add these two calculations into my query? So that my summary report will contain all my reports showing average days listing all my reports.

  2. #2
    Join Date
    Jun 2004
    Location
    Saratoga Springs
    Posts
    24
    You could use one blank report as the main report and then add as many subreports on top of the blank main report as you need. This would be similar to adding unbound subforms to a main form.

    hope this helps

  3. #3
    Join Date
    Jan 2004
    Posts
    37
    Thanks. I went the way of a union query, three ways. It works great

Posting Permissions

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