Limiting the results on a Report (1 Viewer)

Hello. I think that this is going to be a pretty easy one, so here it goes.

I have a report that, when generated, supplies a list of all of the vehicles that have not been serviced for 30 to 59 days, 60 to 89 days, and over 90 days.

The problem is that each vehicle has MANY services. I just need to show FROM the last service date to today if it's past due.

Here is an example:

• truck #1 was serviced on 1/1/12, 6/1/12, and 1/1/13. Therefore it is current.
• truck #2 was serviced on 1/1/12 and 6/1/12 only.
• Therefore truck #2 is over 90 days past due.
  
  As of right now on my report, ALL 5 services are showing up. What I'm looking for is ONLY truck #2, (with it's last service date of 6/1/12) to show up.

Does this make sense to anybody???

Everything on the form is working fine except for this feature.

Thanks all!

Here is one way to do it:

SELECT TruckNo.[Truck no], Max(ServicedLast) AS MaxOfServicedLast, DateDiff("d",Max([ServicedLast]),Now()) AS [Days since last service]
FROM TruckNo INNER JOIN TruckService ON TruckNo.[Truck no] = TruckService.[Truck no]
GROUP BY TruckNo.[Truck no]
HAVING DateDiff("d",Max([ServicedLast]),Now())>90;

Click to expand...

Hey, thanks a ton JHB for jumping in on this one with me!

Now you know, from working with me in the past, that I am a mechanic, and in NO WAY a programmer of any sort! Therefore, if you don't mind, I will need further clarification.

As you have seen from my previously uploaded database, I already have a "SERVICE" table that tracks all of the services performed on each of our vehicles. So, are you suggesting that I create another, simpler version of that table that holds ONLY those two fields listed, which would be [Truck no] (or, in my case [tb_CUA]) & [ServicedLast] (which I do not currently have a column for? And then create a query off of that new table? And if so, how do I get the [ServicedLast] column to function properly?

Or are you suggesting that I just add [ServicedLast] into my already existing "SERVICE" table, and create a query from that table?

Then, the VBA code that you listed would go into the Report, as the "Control Source"? Or on the button/link that runs that particular report?

BTW, If you remember, we just solved the "Past Due by 30, 60 & 90 Days" here.

The problem is that is is returning ALL records of services. Does this change the way that you think that I should do it?

(I'm sorry that I didn't include this part in my initial post! I was finishing a job and trying to get it in before running out the door for the weekend.)

Thanks All!!!

(BTW, I learned in the past to let everyone know that I also have this question going in another Access Forum, here. Feel free to jump in on either one. Thanks!)

OK, this issue has been solved here. Even though pbaldy says that he didn't write this article "just" for me, it sure looks like he did!

Thanks JHB & pbaldy for your continued help!