Monthly event

[Mrsostrich]

Not sure if this is a query or VBA topic

What I want to do is use Access for an automated rent book. I need to add an amount of rent on to a bill each month, so that I can then subrtract payments and keep a running total.

Any ideas please?

 

[Rich]

Calculate the value, based on the current date and the start of the contract, DateDiff will give you the number of Months due

 

[Mrsostrich]

Oh that's a good idea - I hadn't thought of recalculating from the start each time - that also makes it easier to allow for different rent dates for each tennant.

Thanks a lot

 

[Rich]

Be careful with cal months though, DateDiff "m" on it's own will not give the correct result

 

[Mrsostrich]

Ok, I'm probably being dense, but why not and how do I bully it into doing what I want it to?

 

[Rich]

DateDiff("m", #05/08/03#,#01/09/03#) will give the answer as 1, which of course isn't correct. Are you charging cal monthly in advance?

 

[Pat Hartman]

How frequently do you bill? If you do it once a week, then you need an append query that you run once a week as part of your billing process. It should append a row to your rent transaction table for all rents due between dateA and dateB. You supply dateA and dateB at run time. You can use a form and you can even have the form calculate the two dates to minimize data entry.

 

[Mrsostrich]

That's another good idea. The rents are actually billed monthly, but the append query would still work nicely, and would be tidier than just a date diff, it's more like what I had in mind originally. I could then have a similar query working for payment which appends when a payment is entered, then all transactions would be in same table and the difference between due and payments would be calculated, perhaps on a report.

 

[Pat Hartman]

That's my choice for a solution. Keeping separate transactions will help you resolve any data issues.