Invoice Month

[caze]

Hi,

This website is great, I can't seem to figure out one thing. I have a lease database and I want to calculate the invoice month on the fly in a form, this is the code at the moment
=DateAdd("m",Nz ([FREQUENCY],0),Nz([START_LEASE],0))
But it doesn't keep recurring to give me a date of 2008.

An example of a record:-
Start Lease Date - As a Date (15/07/1983)
Frequency of Payment - In Months (12)

So I want to output the invoice month (date), calculated from the two fields essentially I would want this date (15/07/2008) in the invoice month field

Hope this all makes sense, and it's probably really easy, but I'm stuck. Little help please. Many Thanks

Caze

[NeoPa]

Unfortunately it's not clear from what you have already what it is you want.
Your current expression will add simply the number of months in Frequency to your start date. Using your example this will always return 15/7/1984. Regardless of the current date.
The Nz([START_LEASE],0) is also a problem. This will then assume the Lease started on the base date for Access dates (probably sometime around 1900). Not useful for you.
What is the date you want to show? Last payment date (the one before current date) or next one (after today)?

[caze]

At the moment it's working as you said, but I want to get the next invoice date after today.

If a lease started today and the payment was due 12 months from today, my function would work correctly as it would give me next years date in 2009. Let's say it was a five year lease, how would I make it reccur for the next year and the 4 years after that?

Example: A lease started in 15/07/1995 and they paid anually, I would want the invoice date to show 15/07/2008.

[NeoPa]

Although this is possible to do in a formula (=...) as you have it, it's very complicated and this sort of logic lends itself better to coding in VBA.

=NextInv([START_LEASE], [FREQUENCY]) As Date

In the module for your form (or, if this is not in a form, add it to a standard module) include :

Public Function NextInv(varStart as Variant, varFrequency as Variant) As Date
  Dim datNext As Date
 
  ...
End Function

Funny place to stop I know :( But I need to reboot my computer and go out.
Please tell me what you want to do if [START_LEASE] is Null.
When I have this info I can return to this and complete the code.

[caze]

Hi, thanks for helping me figure this one out. This Payment date is being done in a form and in MS Access 2000.

If the [START_LEASE] and the [FREQUENCY] is null, I would want a blank field for the payment date. If either of those is null I would want the Payment Date to be blank also.

So if I had a 5 year lease, the [START_LEASE] would be let's say (15/07/2003) and [FREQUENCY] of payment was (12months), I would want it to do iterations of (12months) per year and show me the next payment date of (15/07/2008)

Thanks in advance!

Caze

[NeoPa]

Cheers for that.
I hope to catch up with things soon (weekend maybe) and I feel this is all here to work with :)
How would you deal with a record where only one or other field is Null though (not both)?
1 answer for Date & 1 for Frequency please ;)

[caze]

Hello,

1. If [START_LEASE] is null then [INVOICE_MONTH] is blank

2. If [FREQUENCY] is null then [INVOICE_MONTH] is blank

The Payment date is dependant on both fields being entered.
Where one field is null then the payment date should be empty/blank.

Have a good weekend!
Thanks again

Caze

[NeoPa]

Try this then.

Public Function NextInv(ByVal varStart as Variant,
                        ByVal varFrequency as Variant) As Variant
  If IsNull(varFrequency) Or IsNull(varStart) Then
    NextInv = Null
    Exit Function
  End If
  NextInv = CDate(varStart)
  Do While NextInv < Date()
    NextInv = DateAdd("m", varFrequency, NextInv)
  Loop
End Function