Need Help with a Date Query

[dgaletar]

He guys, this is probably an easy one for you, but I have a report that when I run it, returns a listing of vehicles that both have recently been and still need to be serviced.

The query that I am using to run the report is titled ""qryMaxDates", and has the following SQL:

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

The report also runs a Macro with the following code:

Public Function pastdueAll(ByRef dtVal As Date) As String

Select Case DateDiff("d", dtVal, Date)

Case Is <= today
pastdueAll = "Current"
Case Is > 1 / 1 / 2013
pastdueAll = "Due for Service"

End Select

End Function

PROBLEM: What I am trying to do is to return a list of vehicles that are current (vehicles that have been serviced within the last 365 days = Current), and that are NOT current (vehicles that have not been serviced, or have not been serviced within the last 365 days = Not Current).

Any thoughts???

 

[pbaldy]

Your Select Case is a number of days, but the Case statements are expecting a date. By the way, that's VBA code, not a macro.

 

[dgaletar]

Paul!!! Great to hear from you again! It's been some time now. Hope you are doing well!

So, what would you suggest for the VVVVBBBBBAAAAA coding? Something like "<365"??? If it's that, I can do that. But if it's a date, how do you make it a 'moving' date???

 

[pbaldy]

I'm doing well, unless you're mocking me with " VVVVBBBBBAAAAA coding".

I'm fine with how you started it, you just have an apples and oranges comparison. Yes, I would think something like < 365 would work.

 

[dgaletar]

Why would I mock someone who has provided soooo much help to me with this project??? I wouldn't! More like teasing you a little!?!

Anyway, so I just tried this:

Public Function pastdueAll(ByRef dtVal As Date) As String

Select Case DateDiff("d", dtVal, Date)

Case Is < 365
pastdueAll = "Current"
Case Is > 365
pastdueAll = "Due for Service"

End Select

End Function

and it worked fine, except it didn't return anything that does not have a date yet. Is there a way to get them to show up???

 

[pbaldy]

Teasing I can handle; God knows I dish it out a lot.

I would expect an error if there's no date, but what do you want done if it's not there? You can test before the Select/Case, like

If Not IsDate(dtVal) Then
  pastdueAll  = "Whatever"
  Exit Function
End If

 

[dgaletar]

If there is no date, then it just needs to fall into the "Due for Service" category. I added the code you suggested as follows:

Public Function pastdueAll(ByRef dtVal As Date) As String

Select Case DateDiff("d", dtVal, Date)

Case Is < 365
pastdueAll = "Current"
Case Is > 365
pastdueAll = "Due for Service"
If Not IsDate(dtVal) Then
  pastdueAll = "Whatever"
  Exit Function
End If

End Select

End Function

...and nothing changed. :banghead:

Woops... just re-read your thread and moved your code...

Public Function pastdueAll(ByRef dtVal As Date) As String
If Not IsDate(dtVal) Then
  pastdueAll = "Whatever"
  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

...but still no difference.

 

[Brianwarnock]

As Paul stated , test before the select case

Brian

Oh I see that you have moved it

 

[dgaletar]

Um... I think that I did. I didn't at first, but that I did and it made no difference.

 

[Brianwarnock]

It is a while since i did this but I think that if dtVal can be null it should be defined as a variant.

Brian

 

[pbaldy]

What does the variable contain if there's no date? Perhaps:

If Len(dtVal & vbNullString) = 0 Then

 

[pbaldy]

It is a while since i did this but I think that if dtVal can be null it should be defined as a variant.

Brian

I agree; that's why I said I would expect it to error.

 

[dgaletar]

No errors... I just tried this:

Public Function pastdueAll(ByRef dtVal As Date) As String
If Len(dtVal & vbNullString) = 0 Then
  pastdueAll = "Whatever"
  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

...and still nothing.

 

[pbaldy]

Can you post the db here?

 

[dgaletar]

...as requested. To run the report, go to the 'View "Service Due" Reports' link under "tasks" & click on the arrow next to it. Select "Service: General Fleet Due" and click "Open".

 

[pbaldy]

I get a "can't find the db" error trying to open it, since it's not in your path. Don't have time to debug at the moment.

 

[dgaletar]

No worries. When you can... THX!

 

[JHB]

You have not attached the database, the ZIP-file only contains the path to the database on your system, (look at the attached size, it is only 1.2 KB) .

 

[dgaletar]

Ooooops! Sorry!

 

[pbaldy]

What am I looking for? When I run that report, all records have a date, and all happen to be current. If I change the test to 30 days, some are current and some are due for service, as I would expect.