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

[dgaletar]

(https://www.accessforums.net/databas...0-a-21753.html)

Hello. I am also fairly new to Access, and am completely self taught. I actually have a very similar request; to create a report that shows Vehicle numbers that have not been serviced in the last 30 to 59 days, 60 to 89 days, and over 90 days.



I tried to use the information above but it didn't work.

The table that I have the "Service" dates in is surprisingly called "SERVICE". Within that table I have two fields that I assume I will be working with: tb_CUA (which is the vehicle number that we have assigned to it), and Date (which is a list of dates that the vehicle was serviced).

I created a MACRO like suggested above, and named it "30_60_90", but am a little confused as to what I should be doing next.

I tried creating both a form and a report and put

Thats the easiest then in your Form, Report you add a text box and in the control source you put in =IIF(isNull([datepaid]),pastdue(invoicedate),"")

into the control field of the Text Box, but it keeps returning the value #Name?.

Any and all suggestions would be GREATLY appreciated!

[pbaldy]

The original thread doesn't appear to mention a macro. It mentions a function named pastdue, which would need to be copied into a standard module.

[dgaletar]

Hey Paul... good to see you again! Somehow your post has managed to confuse me even more! I was so excited to think that I knew what I was doing... knowing where the "Macro" button was, and being able to copy and paste the code into it!

Now your saying that it says "Module"??? What the heck is a Module? (lol)

OK, so I opened a new Module, clicked on the "down arrow", and that's it! I'm stuck there! (just when I think I'm making progress...)

Would you mind dumbing it down for me a bit???

As always, thanks!!!

[pbaldy]

Well, I see:

"In a Module add the following"

With the database open, hit Alt-F11 which should open the VBA editor. Click on Insert then Module and paste the code there. Name the module anything BUT pastdue, as the module can't have the same name as a sub or function.

[dgaletar]

OK, that much is done. I await your next instructions Sir...

[pbaldy]

Does it work now?

[dgaletar]

No. I created a blank form and added a textbox. In the "Control Source" for that text box I entered "=pastdue([«dtVal»])".

When I view the form, the same error comes up; #Name?.

[pbaldy]

Can you post the db here?

[dgaletar]

OK... here she is: Vehicles.zip

Form1 is the form that I created for this.

[pbaldy]

It's an unbound form. It doesn't know what the field is. Either try it on a bound form where that field is available, or use it in a query where that field is present.

[RayMilhon]

Thanks Paul, I'll watch this one now.

[pbaldy]

This thread has a sample db, so jump in if you see a solution.

[RayMilhon]

Did notice one thing Don't know if I rushed in my original post or it was just copied wrong but the statement where the Past Due is 61 to 90 Days should say pastdue = "60 days" not "90 Days"

[dgaletar]

Hey guys... sorry about the double posting. I lost the other post for a while so I thought that it might have gotten erased, and then I didn't know how to combine it. I'll only use this one now.

Ray, I did notice the "60 days" thing and fixed that. Thanks.

And, this morning I did get this to work... in a way. I created a report in the wizard using the "SERVICE" table. It simply has the CUA# (tb_CUA) and a text box. I used the Module as the Control Source for text box, and it works.

The problem is that I was looking for a simpler way to get these results. With this method it prompts you to enter a date in order for it to return any results. I was simply looking for a report that would, when run, automatically show you this information from today's date.

Is that hard to do from here???

[RayMilhon]

Copy what you put into the control source and post it here. Something's not right.