Complex (I think!!) Date Query

[NigelBrown]

I have an Access 2002 table with contract_start and contract_end dates, plus annual_payment and payment_frequency (in months i.e. 0 (net 30 days), 3 (quarterly), 6 (half-yearly) and 12 (annually).

I need to extract a query that will tell me when the payments are due by working back to the start_date and calculating if a payment is due in the next, say, 30 days based upon the payment schedule and up to contract_end date.

Contracts can last for five years so the start_date could be up to five years ago.

In this way I can issue a report to identify what invoices need raising for the coming month.

Many thanks

 

[SikSlk]

Hi, dont know if this will help (especially since i cant spell for #$@%). But I needed something similar but im not that clever with access yet so i hacked a few ideas together that I came accross on this forum.

I madea query with the appropriate feilds and then added three new feilds by adding the following:

Note: I have a frquency coloum which has a single digit like yours to represent number of months. ie. 3, 6, or 12

1: Frequency (D): [WIS]![Frequency]*30
*this turns the single digit to a rough day count*
2: D Since today: DateDiff("d",[Training]![Date of Training],Now())
*this calculates the day since the last training date column - change to your respected column*
3: needed: [D Since today]-[Frequency (D)]
*this gives a value on days which if positive means it is due for training*

so under frequency i would search for >0 (since not all mine had a frequency)
also >0 for the needed coloum to should all positive numbers

dont know if it helps but I hope it does

 

[NigelBrown]

Thanks Sikslk, I'll give that a try and let you know. I was beginning to think I'd beaten everyone

 

[NigelBrown]

SikSlk,

You put me on the right track but I had to add some stuff, some of which is actually redundant but here it is in case it's of use to you. Many thanks

1. Number of Annual Payments: IIf([contract_billing_frequency]>0,12/[contract_billing_frequency],0)

2. Amount of Period Payment: IIf([Expr]>0,[contract_annual_maintenance_charge]/[Expr],0)

3. contract_maintenance_start_date

4. contract_maintenance_end_date

5. Billing Frequency (Days): [contract_billing_frequency]*30

6. Contract Duration (Days): -Fix(DateDiff("d",[contract_maintenance_end_date],[contract_maintenance_start_date]))

(Note: the Minus sign converts a negative number to a positive and the “Fix” reduces the resultant number to the lower integer e.g. 8.7 becomes 8 (The “Int” command would raise 8.7 to 9)

7. Total Number of Payments During Contract: -Fix(DateDiff("d",[contract_maintenance_end_date],[contract_maintenance_start_date])/[Frequency])

8. Payments Made to date: Fix([days Since start]/[frequency])

9. Payment Due (Number of days from now): ([frequency]*[Payments Made])+[Frequency]

10. Days Since Start of Contract: DateDiff("d",[contract_maintenance_start_date],Now())

11. Next Payment Due in Days: [Payment Due]-[days since start]

12. Next Payment Due Date: Now()+[Due Days]