Help with VBA criteria for a report

[gleegeld]

Hi guys,

I need to create a report that only shows the products that have not had their invoices paid since the effective date. All of the products have different effective dates and pay cycles -- i.e. annual, monthly and quarterly. So if a monthly payment is missing, then I want that to show up on the report.

Any help would be great,

Gleegeld

 

[jfgambit]

Do you have a field that identifies whether the product is paid monthly, quarterly, etc.?

"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[hjgoldstein]

If you have a field in your table with "Date Paid" or some other indication that payment has been received, you can set up a small query with the criteria of that field set to "Is Null" or whatever indicates the negative status and then use the query as a filtername in the VBA code calling the report.
The syntax is:
DoCmd.OpenReport reportname [,view][,filtername]
where filtername is the name of the query.

Hope this helps.

 

[gleegeld]

Jfgambit-
Yes we have a a "pay cycle" field that list whether it is paid annual, quarterly, monthly or bi-monthly.

hjgoldstein-
I don't really understand how that would work for me. I guess I should be a bit more clear. When we get invoices in from vendors, an accountatnt enters the information in-- invoice number, invoice date, amount etc. My boss wants a reports tha populates only the products that have missed their monthly, quarterly or annual payment. But, I am also using effective dates for the products, so it could runs from 3/10/03-3/01/04 and it some have maintenace paid monthly, quarterly etc. between that period. He want a report that tells him when is has missed a payment. But am clueless as to what would be the best approach to this.

Again, any script would be great.

Thanks,
Gleegeld