A “MaxOfDate” Issue on One of Our Reports

Hello,

I am having a “MaxOfDate” issue in our database, and I can’t seem to figure it out.

Here’s what’s going on:

DATABASE FUNCTION:

We have a database designed to track the information for our fleet of vehicles, and to keep track of the services that were performed on them. One particular set of vehicles (from the “Department of Public Safety (DPS)” are required to be serviced monthly, while the rest are on an as-needed basis.

To help us accomplish this task we designed a report titled “Service: DPS Due Report”. It tells us which of the DPS vehicles have not been serviced in the last 30 days, 60 days, and 90 days.

The report is laid out as follows:

CUA# (the assigned vehicle number)
AlternateID (the name that the department gives to it)
Date of last PM (Preventive Maintenance) service
Status (whether it is “Current”, “30 days past due”, “60 days past due”, or “Over 90 days past due”

INFORMATION THAT MAY HELP:

The “CUA#”, “CustomerID”, and/or “Customer Number” are all the same thing, and all refer to the vehicles assigned number.

DATABASE DESIGN (relating to this issue):

The “Service” table is setup as follows:

ServiceID (AutoNumber)
CUA# (tied to the “CUA#” on the master table, the “Vehicles” table)
Date (the date that the service was performed ((and I know that we shouldn’t have used the word “DATE”!)))
Mileage (the mileage of the vehicle at the time of the service)
WorkItem (category of the work that was performed ((from a look-up table)))
SubWorkItem (the sub- category of the work that was performed ((also from a look-up table)))

The Query that is used for the “Service: DPS Due Report” is titled “qryMaxDPSDates”, and is setup as follows:

Field: CustomerID; Table: Service; Total: Group By
Field: AlternateID; Table: Vehicles; Total: Group By; Criteria: Like "dps*" Or Like "B-*"
Field: Date; Table: Service; Total: Max; Sort: Ascending

The SQL statement for that query is:

SELECT Service.CustomerID, Vehicles.AlternateID, Max(Service.Date) AS MaxOfDate
FROM Vehicles INNER JOIN Service ON Vehicles.CustomerID = Service.CustomerID
GROUP BY Service.CustomerID, Vehicles.AlternateID
HAVING (((Vehicles.AlternateID) Like "dps*" Or (Vehicles.AlternateID) Like "B-*"))
ORDER BY Max(Service.Date);

Two Modules titled “30_60_90” that are setup as follows:

Option Compare Database
Option Explicit

'Used for our DPS vehicles ONLY
Public Function pastdue(ByRef dtVal As Date) As String
Select Case DateDiff("d", dtVal, Date)
Case Is < 30
pastdue = "Current"
Case 31 To 60
pastdue = "Due for Service"
Case 61 To 90
pastdue = "Over 60 days"
'Case Else
Case Is > 90
pastdue = "Over 90 days"
End Select
End Function

'Used for all vehicles that ARE NOT DPS vehicles
Public Function pastdueAll(ByRef dtVal As Variant) As String
If Len(dtVal & vbNullString) = 0 Then


pastdueAll = "No Record Yet"
Exit Function
End If
Select Case DateDiff("d", dtVal, Date)
Case Is < 365
pastdueAll = "Current"
Case Is > 365
pastdueAll = "Due for Service"
End Select
End Function

Here is what I have found thus far:

If I use the the “MAX” option in the “Date” column of the “qryMaxDPSDates”, I get this:

…nothing dated past September of last year.

If I use the the “MIN” option in the “Date” column of the “qryMaxDPSDates”, I get this:

…more current dates, but still not accurate.

The actual last service performed (MaxOfDate/MinOfDate) on “CUA# 162 – B-60 (Little Ugly)” was today, 03/21/2014. But, as you can see, that is not included in any of the results! And, just to be sure that it is just not “updated” or something for today, the same vehicle was PM serviced on 03/05/2014, and that date isn’t displaying either.

AAAAArrrrrggggggg!!! Any ideas???

I don't see the functions being called. Setting the functions aside, perhaps the service for "Little Ugly" that was performed is not being included in the query for a reason other than the date field.

If you do a basic SELECT query and do not use the totals oprion "Group By" can you get the record to appear? If not the GROUP BY then perhaps your HAVING clause is the culprit.

Hey "ItsMe". Thanks for the quick response! OK, I'm "kinda" following you. I created a simple select query and all of the dates for "Little Ugly" showed up. I even put its number (162) in the "criteria" field and it still worked fine.

What might be the next step???

Start adding back criteria until the record is no longer retreived. When you identify the criteria that causes an issue, isolate it and try the others to be sure it is not more than one condition.

OK, HOLD THE PRESS!!! In an attempt to figure this out, I kept playing with the select query that I created in response to your last post. Here's what I found:

• To start, I only had 3 fields in the query:
  
  • CustomerID
  • Date
  • ServiceID
    ...with no totals line and the query ran fine.
  
  
• I then added the field "AlternateID" and ran it, and it ran fine again.
• I then added "Like "dps*" Or Like "B-*"" to the Criteria field, and it ran fine again.
• I then added the "totals" line, and changed "Date" from "Group By" to "Max", and I got the same results as the last run, all DPS dates.
• I then deleted the "ServiceID" field and ran it, and the results came back like my original post, with all of the dates being in Sept. of 2013.

Does this help at all???

Where do you actually call these functions?

Um, I think that you mean in my report!?!? If so, there is a text box on the report titled "Status". The "Control Source" for it says "=pastdue([MaxOfDate])". Does that help???

WAIT A MINUTE... Where the heck did you come from orange??? I should just hire you as my database assistant!!! lol

It seems that having a totals query while using serviceID is the solution then? To be honest I am having a little trouble following the description of what works and what does not work.

A totals query will not eliminate records but it may include some records in a group that you do not want them to be included in. I guess the ServiceID is the PK for the vehicle.

I guess the ServiceID is the PK for the vehicle.

No. The “CUA#”, “CustomerID”, and/or “Customer Number” are all the same thing, and all refer to the vehicles assigned number.

To be honest I am having a little trouble following the description of what works and what does not work.

OK, where did I lose you?

Originally Posted by dgaletar

OK, where did I lose you?

Pretty much from the beginning. In post #5 you mentioned something is working. Why don't you start over and tell us what you are now using and what problem there is now, if any.

OK, from what I can figure, when the "ServiceID" field is included in the query, it returns ALL of the dates that the vehicle was serviced. When the "ServiceID" field is NOT included in the query, it returns ONLY the dates of September 2013.

Included where, in the SELECT statement? Post the SQL that causes all of the dates to be returned.

SELECT Service.CustomerID, Vehicles.AlternateID, Max(Service.Date) AS MaxOfDate, Service.ServiceID
FROM Vehicles INNER JOIN Service ON Vehicles.CustomerID = Service.CustomerID
GROUP BY Service.CustomerID, Vehicles.AlternateID, Service.ServiceID
HAVING (((Vehicles.AlternateID) Like "dps*" Or (Vehicles.AlternateID) Like "B-*"))
ORDER BY Max(Service.Date);

Using the SQL you posted in post #13, how does this relate to the functions you posted in post #1?

SELECT Service.CustomerID, Vehicles.AlternateID, Max(Service.Date) AS MaxOfDate
FROM Vehicles INNER JOIN Service ON Vehicles.CustomerID = Service.CustomerID
GROUP BY Service.CustomerID, Vehicles.AlternateID
HAVING (((Vehicles.AlternateID) Like "dps*" Or (Vehicles.AlternateID) Like "B-*"))
ORDER BY Max(Service.Date);

Well, from what I can see, the only difference is that in post #1, "Service.ServiceID" is missing from the SELECT & GROUP BY lines.