Im trying to create a report from several queries.
The only difference in the querys is the calculation to determine a number of days since the date received.
This is the first query:
SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD]
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING")) And (((tblAPPLICATIONS.[DATE RECEIVED])<=Date()-182));
I want this in a report
Then I want the report to show the results of this query just below the results of the first query.
This is the second query:
SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD]
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING")) And (((tblAPPLICATIONS.[DATE RECEIVED])<=Date()-152));
Then I want the report to show the results of this query just below the results of the second query.
This is the third query:
SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD]
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING")) And (((tblAPPLICATIONS.[DATE RECEIVED])<=Date()-122));
and I want this to go on for 3 more queries just like these except for the neg. number will change. I also want the query results to be seperated from each other by a space or something. Each query uses the same fields from the same table.
Is this something that I should be doing with one or several queries or some other way? Could you help me understand how to do this?
I have tried this query but got an error message:
SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD], IIf(tblAPPLICATIONS.DATE RECEIVED)<= Date() - 182, "Period1", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 152, "Period2", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 122, "Period3", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 92, "Period4", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 62, "Period5", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 32, "Period6","Older")))))) As Period
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING")) And (((tblAPPLICATIONS.[DATE RECEIVED])<=Date()-32));
I now get this message:
Syntax error (missing operator)in query expression IIf(tblAPPLICATIONS.DATE RECEIVED)<= Date()
The only difference in the querys is the calculation to determine a number of days since the date received.
This is the first query:
SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD]
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING")) And (((tblAPPLICATIONS.[DATE RECEIVED])<=Date()-182));
I want this in a report
Then I want the report to show the results of this query just below the results of the first query.
This is the second query:
SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD]
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING")) And (((tblAPPLICATIONS.[DATE RECEIVED])<=Date()-152));
Then I want the report to show the results of this query just below the results of the second query.
This is the third query:
SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD]
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING")) And (((tblAPPLICATIONS.[DATE RECEIVED])<=Date()-122));
and I want this to go on for 3 more queries just like these except for the neg. number will change. I also want the query results to be seperated from each other by a space or something. Each query uses the same fields from the same table.
Is this something that I should be doing with one or several queries or some other way? Could you help me understand how to do this?
I have tried this query but got an error message:
SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD], IIf(tblAPPLICATIONS.DATE RECEIVED)<= Date() - 182, "Period1", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 152, "Period2", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 122, "Period3", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 92, "Period4", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 62, "Period5", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 32, "Period6","Older")))))) As Period
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING")) And (((tblAPPLICATIONS.[DATE RECEIVED])<=Date()-32));
I now get this message:
Syntax error (missing operator)in query expression IIf(tblAPPLICATIONS.DATE RECEIVED)<= Date()
