Need Help with a Date Query

[dgaletar]

Um, OK. I guess what I am looking for is to show all vehicles. We have 71 vehicles that are currently in service. When I run that report, it only lists like 23 or so as current. Now 7 of the 71 are DPS, so they have their own report. But the rest of the 71 (or 41) should be showing up as "Due for Service". That's my issue.

 

[JHB]

Um, OK. I guess what I am looking for is to show all vehicles. We have 71 vehicles that are currently in service. When I run that report, it only lists like 23 or so as current. Now 7 of the 71 are DPS, so they have their own report. But the rest of the 71 (or 41) should be showing up as "Due for Service". That's my issue.

Hmm - the PM table contains only 53 records, and some of them has more that one date. If you run the qryMaxDates you get 34 record all in all including DPS.
If you run qryMaxDates2 you get 27 records, (34-7 = 27).

Okay - I think I got it, you also want them which are not in the PM table.

Yes, you just confirm that, I see.

 

[dgaletar]

Right. That is because, since we have started using this database (only a few months ago), only 27 (again... not including the DPS) vehicles have been in for service. That would mean that the others are "Due"... right? And therefore should show up on the report as due.

 

[JHB]

Try it now.

 

[dgaletar]

PERFECT!!!! What did you do???

 

[JHB]

Left Join, (and change a little in the report).

SELECT Vehicles.CustomerID, Vehicles.AlternateID, Max(PM.DatePMd) AS MaxOfDatePMd
FROM Vehicles LEFT JOIN PM ON Vehicles.CustomerID = PM.CustomerID
GROUP BY Vehicles.CustomerID, Vehicles.AlternateID, Vehicles.OutofService
HAVING (((Vehicles.OutofService)=False))
ORDER BY Max(PM.DatePMd);

 

[dgaletar]

OK, I copied your VBA code from the database you attached a few messages ago, & I'm guessing I should copy & paste the code that you just posted. But where does it go? Somewhere in the report? Because it looks like SQL coding to me. (And the confusion sets in deeper)

 

[Cronk]

I'll come in late here.

I have not opened the zip but a couple of things
Firstly, should not the source query contain Max(PMDatePMd) in the grouping.
Secondly, the variable being passed to the function is defined as a date, and then you test if it is not a date. I'd change the variable type to Variant.

Max(PM.DatePMd) in the grou

 

[JHB]

OK, I copied your VBA code from the database you attached a few messages ago, & I'm guessing I should copy & paste the code that you just posted. But where does it go? Somewhere in the report? Because it looks like SQL coding to me. (And the confusion sets in deeper)

Ok, the below SQL should be inserted in you query "qryMaxDates" instead of what you have, (sorry I thought you could recognised it! ).

SELECT Vehicles.CustomerID, Vehicles.AlternateID, Max(PM.DatePMd) AS MaxOfDatePMd
FROM Vehicles LEFT JOIN PM ON Vehicles.CustomerID = PM.CustomerID
GROUP BY Vehicles.CustomerID, Vehicles.AlternateID, Vehicles.OutofService
HAVING (((Vehicles.OutofService)=False))
ORDER BY Max(PM.DatePMd);

In the report "Service: General Fleet Due", the control source for "Text14", shold be replaced with the below line:

=pastdueAll(IIf(IsNull([MaxOfDatePMd]);Null;[MaxOfDatePMd]))

And in the module "30_60_90", function "pastdueAll" you must declare "dtVal" as variant instead of Date.

Public Function pastdueAll(ByRef dtVal As Variant) As String

 

[JHB]

I'll come in late here.
Firstly, should not the source query contain Max(PMDatePMd) in the grouping.

How would you group them, (Either do you group, or you are summing, finding the maximum, finding the minimum etc. you can't do both at once)?

 

[Cronk]

Pretty much as you
SELECT Vehicles.CustomerID, Vehicles.AlternateID, Max(PM.DatePMd) AS MaxOfDatePMd, Count(PM.WorkorderID) AS CountOfWorkorderID, pastdueAll([DatePMd]) AS [Service Status]
FROM Vehicles LEFT JOIN PM ON Vehicles.CustomerID = PM.CustomerID
GROUP BY Vehicles.CustomerID, Vehicles.AlternateID, Vehicles.OutofService, pastdueAll([DatePMd])
HAVING (((Vehicles.OutofService)=False))
ORDER BY Max(PM.DatePMd);

By the way, I hope no customer gets to have more than one vehicle.

 

[JHB]

Pretty much as you
SELECT Vehicles.CustomerID, Vehicles.AlternateID, Max(PM.DatePMd) AS MaxOfDatePMd, Count(PM.WorkorderID) AS CountOfWorkorderID, pastdueAll([DatePMd]) AS [Service Status]
FROM Vehicles LEFT JOIN PM ON Vehicles.CustomerID = PM.CustomerID
GROUP BY Vehicles.CustomerID, Vehicles.AlternateID, Vehicles.OutofService, pastdueAll([DatePMd])
HAVING (((Vehicles.OutofService)=False))
ORDER BY Max(PM.DatePMd);.

I see you did not a group by Max(PM.DatePMd), as mention in your former post.

By the way, I hope no customer gets to have more than one vehicle.

Why, (so far I can see, Vehicles.CustomerID is for each vehicle.) ?

 

[dgaletar]

Thanks JHB!!! You nailed it! And wow did you lay it out perfect for a newbie! I wish more people would take the time to understand that everyone doesn't do this for a living.