Past Due (30, 60, or 90)

[claysea]

Let me start off by saying I am self-taught on Access. Having said that, I am trying to create a field that will say if an invoice is current, 30, 60, or 90 days past the invoice date. Additionally, I have a field next to the invoice date that indicates date paid. So what I want is a formula/expression?? to look indicate if an invoice is current, 30, 60, or 90 days past the invoice date (but only when the date paid field is blank). Sorry, I hope I make sense.

[RayMilhon]

There are several ways of doing this but I personally would use the following

In a Module add the following

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 = "90 days"
Case Else
Pastdue = "Greater than 90 days"

end Select

end function

Then call the function where ever you need to.

[claysea]

I don't know what a module is and the information above looks greek to me. Is there something I can add in the Control Source section of a form or report?

[RayMilhon]

Yes you can but it's a lot more complicated. You simply create a module and place the VBA Code there How you create a module Depends on your version of Access. 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),"")

To put it into a control on a form or report requires the following

=IIF(IsNull([datepaid]),IIF(datediff("d",[invoicedate],date())<30,"Current",IIF(datediff("d",[invoicedate],date()) between 31 and 60,"30 Days",IIF(datediff("d",[invoicedate],date()) between 61 and 90,"60 Days","90 Days"))),"")

plus you have to do it every time you want that to display.

[dgaletar]

Yes you can but it's a lot more complicated. You simply create a module and place the VBA Code there How you create a module Depends on your version of Access. 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),"")

To put it into a control on a form or report requires the following

=IIF(IsNull([datepaid]),IIF(datediff("d",[invoicedate],date())<30,"Current",IIF(datediff("d",[invoicedate],date()) between 31 and 60,"30 Days",IIF(datediff("d",[invoicedate],date()) between 61 and 90,"60 Days","90 Days"))),"")

plus you have to do it every time you want that to display.

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!

[RayMilhon]

Did you add the function pastdue from my first post. If not, then access has no idea what pastdue(invoicedate) is and you will receive the error #Name. You say you created a macro my post talked about a module two different things.

[dgaletar]

Got it. I just figured that out (thanks to pbaldy setting me straight. I've never worked with a Module, so he told me how to do it.). So now I have the VBA that you listed above placed in a module named 30_60_90. I then created a 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?.

[RayMilhon]

If you open the Module at the top you should see

Option Compare Database
Option Explicit

Publc Function Pastdue(ByRef dtvalue as date) as string

If not then copy what you have in the module and post it here. I'll take a look

[pbaldy]

Heads up Ray, discussion going on in two threads:

https://www.accessforums.net/access/...0-a-32382.html