# Thread: Several date formulas in query

1. Registered User
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,
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
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. Registered User
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. Registered User
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
•