I’m trying to calculate a field that shows a message that if the Invoice Due Date plus the number of days for the Terms

So if the Invoice Due date is “11/5/05" and the Terms are "30"
It would show the message “Past Due” starting on the day of 12/5/05 and on.

Below is what I thought would work.

Past Due: IIf([Invoice Due Date]="> ([Invoice Due Date]+[Terms]) "Past Due",")

Past Due: Iif(Date() > DateAdd("d",[Terms],[Invoice Due Date]), "Past Due", Null)


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

It's only giving me an error message?

What is the error message?

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

#Name?

Given the code:

Past Due: Iif(Date() > DateAdd("d",[Terms],[Invoice Due Date]), "Past Due", Null)

The #Name Error means that either [Terms] does not exist, or [Invoice Due Date] does not exist, or even the functions Date() or DateAdd() do not exist. Because you didn't get a pop-up box asking you to enter term or invoice due date, I'm guessing that one or both of the two functions, Date() or DateAdd() didn't work. That means you're missing a reference library.

Open any VBA code and click on TOOLS > REFERENCES in the menu. Look for any libraries that say "MISSING!" and try to find them (or something close) in the list under them and click them.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I tried this formula

=IIf(Date()>([InvoiceDueDate]+[Terms]),"Past Due","")
But it doesn’t populate all the ones that should be.

Can you give us a short list of data? Include what you're getting and what you WANT to get as a comparison. Don't forget column headings that reflect the actual name of the field (including calculated ones like Past Due).

What happens when there IS no Invoice Due Date and/or Terms value?



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Buyer Name:Tom (What it should look like)
Vendor: SENTRY INDUSTRIES
PO#: 862
Cost: $13.00
Expected Date: 5/11/2005
Terms: 30
InvoiceDueDate: 6/10/2005
Notes: SAVED
System Notes: Past Due



With Formulas
Buyer Name: Tom
Vendor: SENTRY INDUSTRIES
PO#: 862
Cost: $13.00
Expected Date: 5/11/2005
Terms: 30
InvoiceDueDate: Expected Date+Terms
Notes: Text Message
System Notes =IIf(Date()>([InvoiceDueDate]+[Terms]),"Past Due","")

Make these two changes.

InvoiceDueDate: DateAdd("d", [Terms], [Expected Date])

System Notes: IIf(Date() > [InvoiceDueDate], "Past Due", Null)


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division