recurring invoicing

[Housey]

Hi,

I'm wondering the best way to go about this. I have a situation where a user will click a button once a week and this will query all records that are due to be invoiced (these will be monthly invoices but the button would be clicked once a week as not all fall on the same date).

I then need the ability to query the database for people that are overdue and this will produce a bunch of reports to be sent as letter reminders.

I was thinking to use Datediff and see if the the last invoice date was a month previous, however that wouldn't work and some would slip though.

What's the best way to acheive this?

Thanks in advance.

 

[neileg]

Not enough info on how your db is structured.

 

[Housey]

Not enough info on how your db is structured.

Hi,

Well I've got an contacts tbl which is linked to orders which is linked to payments.

In the orders tbl I have a field called reminders which will hold a value from 0 to 4, this will will be updated everytime a invoice/reminder is sent and will help me determine whether an invoice was sent of is a reminder then which reminder they have been sent, if set to 0 then they just need an invoice to be printed as it must be a new order. If 1 = reminder 1, 2 = reminder 2 etc

These will be sent each week and processed by someone clicking a button

It is possible to loop through like this?:

Start Loop
DoCmd.OpenReport stDocName, , "", strWhere, acHidden
False
DoCmd.Close acReport, "stDocName"
Loop

strWhere would be one of the following, depending on the button they clicked:

strWhere = "WHERE reminder = 0 and datediff("d",date,"&rsOrders![dateLastInvoice]&") >= 23"

strWhere = "WHERE reminder = 1 and datediff("d",date,"&rsOrders![dateLastInvoice]&") >= 10"
strWhere = "WHERE reminder = 2 and datediff("d",date,"&rsOrders![dateLastInvoice]&") >= 10"
strWhere = "WHERE reminder = 3 and datediff("d",date,"&rsOrders![dateLastInvoice]&") >= 10"

I actually need invoices sent each month but not necessarily the same date for each customer so I'm hoping if they are clicking the button once a week then >= 23 days should be ok, giving them a week and 10 days or greater from last invoice date (which will actually be the date any reminder or invoice is printed).

EDIT:
Invoicing happens everymonth, reminders only sent if invoice not paid.

Thanks for any help,