Complex Date Question

[Guest]

Hi there,

Looking for some help with a rather long winded and complicated combination
of SQL statements and queries.

The problem is with a hire scheme system. A customer takes an item at
[StartDate], and starts making payments 3 months after they take it away, for
[SchemeLength] months thereafter.

I am trying to make a form to display the payments that should be taken
between a determined date and today's. For example, between 16/02/2007, until
now, 10/03/2007. I have been using DatePart and getting the integer value of
the days in the month and putting it in a between statement:

Where (DatePart("d",[StartDate]) Between DatePart("d",[Enter Date]) And
DatePart("d",Date())

But as you can imagine, this creates problems across different months
(although is fine for within one month).

Sorting out:

Between ([StartDate] + 3months) And ([StartDate] + 3months + [SchemeLength])
should not be an issue, although any help here is welcome!

Any assistance is greatly appreciated.
Edd

 

[John Nurick]

Hi Edd,

Maybe something like this:
BETWEEN [StartDate] AND DateAdd("M", 3, [StartDate])

Also, check out the DateDiff() function.

Hi there,

Looking for some help with a rather long winded and complicated combination
of SQL statements and queries.

The problem is with a hire scheme system. A customer takes an item at
[StartDate], and starts making payments 3 months after they take it away, for
[SchemeLength] months thereafter.

I am trying to make a form to display the payments that should be taken
between a determined date and today's. For example, between 16/02/2007, until
now, 10/03/2007. I have been using DatePart and getting the integer value of
the days in the month and putting it in a between statement:

Where (DatePart("d",[StartDate]) Between DatePart("d",[Enter Date]) And
DatePart("d",Date())

But as you can imagine, this creates problems across different months
(although is fine for within one month).

Sorting out:

Between ([StartDate] + 3months) And ([StartDate] + 3months + [SchemeLength])
should not be an issue, although any help here is welcome!

Any assistance is greatly appreciated.
Edd

 

[Guest]

Thanks for your response!

I have tried many combinations of datediff and dateadd, and its proving near
impossible! Any further suggestions are appreciated greatly!

Edd

Hi Edd,

Maybe something like this:
BETWEEN [StartDate] AND DateAdd("M", 3, [StartDate])

Also, check out the DateDiff() function.

Hi there,

Looking for some help with a rather long winded and complicated combination
of SQL statements and queries.

The problem is with a hire scheme system. A customer takes an item at
[StartDate], and starts making payments 3 months after they take it away, for
[SchemeLength] months thereafter.

I am trying to make a form to display the payments that should be taken
between a determined date and today's. For example, between 16/02/2007, until
now, 10/03/2007. I have been using DatePart and getting the integer value of
the days in the month and putting it in a between statement:

Where (DatePart("d",[StartDate]) Between DatePart("d",[Enter Date]) And
DatePart("d",Date())

But as you can imagine, this creates problems across different months
(although is fine for within one month).

Sorting out:

Between ([StartDate] + 3months) And ([StartDate] + 3months + [SchemeLength])
should not be an issue, although any help here is welcome!

Any assistance is greatly appreciated.
Edd

 

[John W. Vinson]

The problem is with a hire scheme system. A customer takes an item at
[StartDate], and starts making payments 3 months after they take it away, for
[SchemeLength] months thereafter.

I am trying to make a form to display the payments that should be taken
between a determined date and today's. For example, between 16/02/2007, until
now, 10/03/2007. I have been using DatePart and getting the integer value of
the days in the month and putting it in a between statement:

Where (DatePart("d",[StartDate]) Between DatePart("d",[Enter Date]) And
DatePart("d",Date())

But as you can imagine, this creates problems across different months
(although is fine for within one month).

Sorting out:

Between ([StartDate] + 3months) And ([StartDate] + 3months + [SchemeLength])
should not be an issue, although any help here is welcome!

Any assistance is greatly appreciated.

This can be done with the help of a handy auxiliary table. Create a table
named Num (some folks, more erudite, call it Iotas) with a single Integer
field N. Fill it manually with values from 0 through the maximum number of
payments you'll ever need (be generous, it's a tiny table even with 10000
records).

Create a Query

SELECT <whatever fields>, DateAdd("m", N, [StartDate])
FROM <yourtable>, [Num]
WHERE N < [SchemeLength]

This query will display all of the payment dates.

John W. Vinson [MVP]