Customer keeping track of invoices received and paid.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lcrunicorn - 20 Dec 2005 19:06 GMT

I am trying to figure out the best way to get an alert in my Access 2002
Database when an invoice that is for an active vendor has not been received.

Ex. I receive invoices on a daily bases. If an invoice was received last
month, it is assumed that if the account number from that vendor is active, I
am waiting to receive the invoice.

Does anyone have any idea on how I can go about doing this? If I am not
clear, please ask me questions and I will do my best to answer.

I don't do programming; but I am pretty good with setting up basic stuff in
the database.

Any help is greatly appreciated.

Lori

Reply to this Message

Pachydermitis - 20 Dec 2005 19:42 GMT

Lori,
I am guessing that if they paid the 15th of last month, you would like
a warning on the 15th of this month if they didn't pay
I don't know what you tables look like, but this example may help.

tInvoices
InvoiceDate | CustomerName
------------------------------------------
11/20/05 | Fred's Auto
11/21/05 | Freda's Deli

This query will give you everyone who has not paid within the current
monthly period, and who had an invoice a month ago.
If you make this into a query and then a report, just run at the end of
your day for your warnings.

SELECT DateAdd("m",1,Max([InvoiceDate])) AS DueDate,
tInvoices.CustomerName
FROM tInvoices
GROUP BY tInvoices.CustomerName
HAVING (((DateAdd("m",1,Max([InvoiceDate]))) Between
DateAdd("m",-1,Date()) And Date()));
HTH

Reply to this Message

pietlinden@hotmail.com - 20 Dec 2005 19:43 GMT

Assuming each invoice has a due date and a paid date, you could open a
report when your database opens and set the filters for the report to
be something like

WHERE DueDate<Date+7
AND DatePaid Is Null

Then in the OnNoData event of the report, you can cancel the open
event. (In other words, if there's no data for the report's
recordsource (a query, probably), then the report will just close.
Otherwise, the items that are coming due or are overdue will show up.

Reply to this Message