Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  fomular to caculate days in access report

Author  Topic  Hillary Starting Member 1 Post Posted - 2004-07-10 : 15:14:55 I have a DB which produces several reports. Each report has a different fomular 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 fomulars to caculate 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 caculations into my query? So that my summary report will contain all my reports showing average days listing all my reports.