Continuation - Past Due (30, 60, or 90)

[dgaletar]

Option Compare Database
Option Explicit

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 = "30 Days"
Case 61 To 90
pastdue = "60 days"
Case Else
pastdue = "Over 90 days"

End Select

End Function

[dgaletar]

I've tried using a few different expressions that I came across, like:


DateValue(CStr(Now()))
Date()
=Date()
Now()
Date =Now()

...and others in the code, but none of it worked.

[pbaldy]

I bound your form1 to the service table and added a textbox bound to the Date field (bad name, as it can be confused with the Date() function). This appears to work in the textbox:

=pastdue([date])

[dgaletar]

Hey Paul, I'm sorry to sound stupid, but... what? I have a blank form, "Form1". The form is bound to the "SERVICE" table. A text box is added and bound to the "Date" field of said "SERVICE" table. Now your saying to add another text box, and in the "On Click" property box, place "=pastdue([date])" in the VBA? Is that added to the rest of the VBA code that I have been using? And, if so, where do I place it?

Sorry man... this is hard stuff for a mechanic!!! lol

[pbaldy]

No, that was the control source. The function remained as is (I think).

[dgaletar]

OK, I think we may have strolled a little bit off track here (I really have to get better with Access terminology so I can stop creating all of this extra work for the people trying to help me). Let me recap:

...and please let me start by eliminating the word "Form" for this topic. I will be working with a "Report" instead.

TASK: To be able to view a report of all vehicles that are 30, 60 & 90 days past due for service.

CURRENT PROCESS: On the databases' navigation page I have a link. That link entitled "Vehicles that are 30, 60 & 90 Days Past Due", when selected, opens the report to show the collective results.

ISSUE: As of right now, when you select that link (Vehicles that are 30, 60 & 90 Days Past Due), an "Enter Parameters Value" box appears where the end user is required to enter a certain date. Instead, what I would like to see happen is for the report to automatically generate the report from the current date, "today's date" if you would.

Does that make more sense?

[pbaldy]

There is no such link or report on the sample I have, but a textbox with the same control source should work on a report as well. Just tested on the Service_All report.

[dgaletar]

Sorry 'bout that! RayMilhon had me create all that.

Here is an updated database with that info so you can see what the heck I am talking about.

Vehicles.zip

[pbaldy]

Have you tried what I posted? You've still got that funky bracketed non-existent field name in there.

[dgaletar]

I honestly haven't tried what you posted because I am pretty confused as to what you meant. Are you talking about putting "=pastdue([date])" somewhere into the 30_60_90 Module?

[pbaldy]

No, as I mentioned in posts 20 and 22, that's the control source of a textbox. See attached.

[dgaletar]

WOW!!! It works PERFECTLY!!! Thank you. I guess that I am a visually oriented man, so THANKS for the screenshot!

If I may, one last question on this report...

My partner mechanic saw the report as I was running it, and he LOVED it!!! But he asked if there was a way to add the "Alternate ID" name/number between the vehicles number (CUA#) and the status of the vehicle? (The reason is that most of the vehicles with an Alternate ID get serviced every month, while the rest get serviced once every ninety days.) This wouldn't be a problem if the control source "Alternate ID" was a part of the SERVICE table, but it isn't.

Should I add it into the SERVICE table? Or is there another way to call it into the report?

[pbaldy]

Sure, you base the report on a query that joins the two tables together. That lets you include fields from both tables on the report.

[dgaletar]

OK, back to queries. So, create a query & add both tables; SERVICE & VEHICLES. Add all of the fields from SERVICE & Alternate_ID from VEHICLES, right? Then, base the report off of that query, right?

[pbaldy]

You've got it!