A “MaxOfDate” Issue on One of Our Reports

[ItsMe]

I am refering to


Public Function pastdue

and
Public Function pastdueAll

How do these functions relate, within your app, to the SQL you posted in post #13?

It appears you use these functions to retrieve the records you desire.

[dgaletar]

Ah yes, I think that I see what you are getting at. I have a Report named "Service: DPS Due Report". It's record source is "qryMaxDPSDates". On the report itself the
second to last column (from the right) is "Date of Last PM Srvc." The "Control Source" for that is "MaxOfDate". The last column is titled "Status". The "Control Source" for that is "=pastdue([MaxOfDate])".

Is that what you were looking for???

[ItsMe]

Did you inherit this DB?

I guess that answers my question regarding the functions. Is the SQL in post #13 not desireable? Can you use the functions to fine tune the results?

[dgaletar]

Did you inherit this DB?

No, But there were several people (mostly from these forums) that helped me with the build as it needed to be up and running pretty quickly.

Is the SQL in post #13 not desirable?

No. It is still giving me all of the dates for the DPS vehicles, not just the most current ones.

Can you use the functions to fine tune the results?

I could if I knew how to...

[ItsMe]

It may be as simple as adding a statement onto your HAVING clause that uses the Between operator. I looked at post#1 again and still can not determine what you are trying to do. I guess you need to open a report. When I open a report it is usually to print or export it as a PDF file. Otherwise, I will use a form.

Depending on what you are doing, opening a report, printing a report, or viewing data AND how you open the report will dictate the best approach to take.

Can you provide a simple, plain English, explanation of what you are trying to accomplish from a business perspective. Also, how are you determining the criteria that will, in turn, determine what is included in the report? For instance, do you need all of the vehicles that are past due? Do you need only select vehicles that may be due for service soon?

[dgaletar]

I looked at post#1 again and still can not determine what you are trying to do. I guess you need to open a report.

Yes, I need to open a Report in the "Print Preview" format for viewing, and eventually printing (if needed).

It may be as simple as adding a statement onto your HAVING clause that uses the Between operator.

Unfortunately I don't understand this at all.

Can you provide a simple, plain English, explanation of what you are trying to accomplish from a business perspective.

Yes, here it goes. In Post #1 I stated:

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.

What does that mean to a lay person. Well, if you have a newer vehicle you will notice that, if you don't get your oil changed at the right interval your "change oil soon" light will come on. That is designed to remind you that your vehicles' oil is ready to be changed. You then take it to a service station and have the service performed. Once that service has been performed, the service technician will reset your "change oil soon" light so that it starts the cycle over again.

Well, our security vehicles (we have 7 of them) pretty much run 24 hours a day, 7 days a week. Therefore we, the service technicians, need to tell them when they are due for an oil change. We try to service these vehicles every 30 days to keep them in the best condition possible.

When the vehicle comes in for this service, (say Security Vehicle #1 for arguments sake), we enter the service date into our database under that specific vehicles' record.

- If, at that point we run this report, the report will say that Security Vehicle #1's "Status" is "Current", and that the "Date of Last PM Srvc." was on "03/25/2014".
- If we run the report 31 days from today, it will tell us that Security Vehicle #1's "Status" is "Over 30 Days" (from it's last service), and that the "Date of Last PM Srvc." was on "03/25/2014".
- If we run the report 61 days from today it will tell us that Security Vehicle #1's "Status" is "Over 60 Days" (from it's last service), and that the "Date of Last PM Srvc." was on "03/25/2014".
- If we run the report 91 days from today it will tell us that Security Vehicle #1's "Status" is "Over 90 Days" (from it's last service), and that the "Date of Last PM Srvc." was on "03/25/2014".

This report is crucial to us knowing which vehicles are NOT current, and what order (date wise) we need to service them in. (I.E: If Security Vehicle #1 was last serviced on 09/19/13 and Security Vehicle #2 was last serviced on 09/18/13, we need to bring Security Vehicle #2 in for service first, as it has been longer since it's last service.)

Also, how are you determining the criteria that will, in turn, determine what is included in the report? For instance, do you need all of the vehicles that are past due? Do you need only select vehicles that may be due for service soon?

You probably have a better understanding of this answer from the explanation above, but just to be safe, we have two (2) reports; one for the Department of Public Safety vehicles, and one for all of the rest of them. (FYI - Neither of these reports are functioning properly.)

The strange thing is that the reports were working just fine until...


OMG... I THINK THAT I JUST FIGURED IT OUT!!! Take a look at this:



As you can see, Access is "Sorting" the "Date" column, and "9" is the highest number (9/24/2013). That's why it keeps showing up as the first one! But when I sort them myself, 3/21/2014 is the MOST CURRENT date!

OK, well... any idea how I can fix this???

[ItsMe]

I glossed over your reply. I will be busy today, so won't have a lot of time to spend on the forums.

When I read your explanation, I continue to believe the SQL in post #13 will be effective and you just need to add criteria to it, as needed. What will need to be determined is how the user interacts with the app so the user can tell the app what the criteria is, entire fleet 30 days or maybe something else.

Then, when I consider that the results you have been receiving all along are not what you thought they were; I will need to take another look and understand what the revelation is.