I am trying to create a report that will return a format for a time frame. My program is for a landscaping business that needs to know who's lawn needs to be cut. Since this a weekly task I need to create a list that can be given to a crew that shows who hasn't been serviced in the past 4,5,6,7etc days. When I run the querry and view the report it shows all visits rather then last time they were cut. I used a expression of <Date()-[Forms]![Work to be Completed]![DaySinceLast]
How do I eliminate the everything but the last day?
mrbernh wrote: > I am trying to create a report that will return a format for a time frame. My > program is for a landscaping business that needs to know who's lawn needs to > be cut. Since this a weekly task I need to create a list that can be given to > a crew that shows who hasn't been serviced in the past 4,5,6,7etc days. When > I run the querry and view the report it shows all visits rather then last > time they were cut. I used a expression of > <Date()-[Forms]![Work to be Completed]![DaySinceLast]
> How do I eliminate the everything but the last day?
Your expression : Date()-[Forms]![Work to be Completed]![DaySinceLast], refers to a control on a form, which inidicates that you are doing a calculated control on a form, and expecting it to have some effect on a query.
If you want to affect the records selected for a report, you need to apply a criteria on the query that is the recordset for the report, which is a separate recordset from that of the form.
Making a lot of assumptions... if you are trying to use the above expression as the criteria in your query, you're probably having trouble because it will use only the single value of the expression which derives from the form's active record at the instant you run the report.
What would probably work better would be to create a calculated field in the query you will use as the report recordset. The calculated field would calculate the days since last service. Then you can put a range criteria on that field.
Example calculated field: DaysSinceLastService:Date()-[FieldnameOfDateOfLastService] This should result in a number.
Example criteria for the calculated field: Between 4 and 7
Tip: Do not try to put a criteria on a calculated field, which is itself dependent upon some other calculated field. -- Frank H Rockford, IL
"mrbernh" wrote: > I am trying to create a report that will return a format for a time frame. My > program is for a landscaping business that needs to know who's lawn needs to > be cut. Since this a weekly task I need to create a list that can be given to > a crew that shows who hasn't been serviced in the past 4,5,6,7etc days. When > I run the querry and view the report it shows all visits rather then last > time they were cut. I used a expression of > <Date()-[Forms]![Work to be Completed]![DaySinceLast]
> How do I eliminate the everything but the last day?
You can to use a subquery to restrict the results to where no rows within the last n days exist for each customer, and return the MAX visit date to show the latest visit per customer, grouping the query by customer, e.g.
SELECT [CustomerID], MAX([VisitDate]) As [Last Visit] FROM [CustomerVisits] AS CV1 WHERE NOT EXISTS (SELECT * FROM [CustomerVisits] AS CV2 WHERE CV2.[CustomerID] = CV1.[CustomerID] AND [VisitDate] > DATEADD("d",[Forms]![Work to be Completed]![DaySinceLast]*-1,DATE())) GROUP BY [CustomerID];
You can of course include other columns with values distinct per customer in the query's SELECT and GROUP BY clauses such as the customer name and address, joining whatever tables are needed to return the relevant columns. The subquery only needs to use the one table, CustomerVisits in this example, and be correlated with the outer query on the one column, CustomerID, however.
Ken Sheridan Stafford, England
mrbernh wrote: >I am trying to create a report that will return a format for a time frame. My >program is for a landscaping business that needs to know who's lawn needs to >be cut. Since this a weekly task I need to create a list that can be given to >a crew that shows who hasn't been serviced in the past 4,5,6,7etc days. When >I run the querry and view the report it shows all visits rather then last >time they were cut. I used a expression of ><Date()-[Forms]![Work to be Completed]![DaySinceLast]
>How do I eliminate the everything but the last day?
On second thoughts you don't really need a subquery. This should do it:
SELECT [CustomerID], MAX([VisitDate]) As [Last Visit] FROM [CustomerVisits] HAVING MAX([VisitDate]) <= DATEADD("d",[Forms]![Work to be Completed]![DaySinceLast]*-1,DATE())) GROUP BY [CustomerID];
Try this -- SELECT [ClientID], Max([VisitDate] AS Last_Visit, (Date() - Max([VisitDate]) AS Days_Ago FROM YourTable GROUP BY [ClientID], (Date() - Max([VisitDate]) HAVING Date() - Max([VisitDate] >3 ORDER BY (Date() - Max([VisitDate]), [ClientID];
You may not need (Date() - Max([VisitDate]) in the GROUP BY.
"mrbernh" wrote: > I am trying to create a report that will return a format for a time frame. My > program is for a landscaping business that needs to know who's lawn needs to > be cut. Since this a weekly task I need to create a list that can be given to > a crew that shows who hasn't been serviced in the past 4,5,6,7etc days. When > I run the querry and view the report it shows all visits rather then last > time they were cut. I used a expression of > <Date()-[Forms]![Work to be Completed]![DaySinceLast]
> How do I eliminate the everything but the last day?
|
