Seemingly impossible date question!

[Edd]

Hi all

Thanks in advance for any help on this matter.

I think the easiest way to describe the problem is to describe the situation
so here goes.

First of all here are all related values variables

StartDate - record specific
InitialPeriod - record specific
PeriodLength - record specific
Date() - today
LastUpdatePoint - independant global variable in unrelated table (date/time)

The setup is a retail store's hire scheme. People take the instrument away
on [StartDate], after paying a deposit that covers them for [InitialPeriod]
in months. They start paying monthly installments after that term, for
[PeriodLength] in months, for which [PeriodLength] + [InitialPeriod] = [total
scheme length] in months, and [StartDate] + [total scheme length] in months =
[end of scheme] date. The system is capable of detecting when this date has
passed, and warns employees to stop taking monthly payments, (eg cancel the
direct debit). The problem comes however when trying to display payments that
are due to go out since the last time payments were checked,
[LastUpdatePoint].

For example, an employee takes all payments due from 20/11/2007 through to
the current Date(), 25/11/07 (the day of which each payment due relying on
the date of the start date, for example if the start date was 23/6/2007). The
new [LastUpdatePoint] goes into the table as 25/11/07 hence the day it was
performed (independant of any records). So when an employee returns on the
28th, the only records to be displayed are ones that require payment (and
hence not the payment due on the 23rd, but from the 25th-28th).

I need a query to display the schemes that have not passed [end of scheme],
that HAVE reached [StartDate] + [InitialPeriod], and within THAT set of
values, schemes for which the DatePart "days" value of [StartDate] is between
the DatePart date values of [LastUpdatePoint] and Date() respectively.

I know this is a mathematical nightmare, but I'm really stuck as you can
imagine!!

Again thanks for any correspondance!

Edd

 

[Robert Morley]

Unless I'm missing something, you've already described your WHERE clause
quite clearly, so it's just a matter of typing it out.

I need a query to display the schemes that have not passed [end of
scheme], that HAVE reached [StartDate] + [InitialPeriod]

BETWEEN [StartDate] + [InitialPeriod] AND [end of scheme]

and within THAT set of values
AND

schemes for which the DatePart "days" value of [StartDate]
is between the DatePart date values of [LastUpdatePoint] and Date()
respectively.

DatePart("d", [StartDate]) BETWEEN DatePart("d", [LastUpdatePoint]) AND
DatePart("d", Date())

Is there something more complex than this that I'm not understanding? (Oh,
and my memory of DatePart is a little foggy, as it's been a while since I've
used Jet...double-check that I've used the right syntax.)


Rob

Hi all

Thanks in advance for any help on this matter.

I think the easiest way to describe the problem is to describe the situation
so here goes.

First of all here are all related values variables

StartDate - record specific
InitialPeriod - record specific
PeriodLength - record specific
Date() - today
LastUpdatePoint - independant global variable in unrelated table (date/time)

The setup is a retail store's hire scheme. People take the instrument away
on [StartDate], after paying a deposit that covers them for [InitialPeriod]
in months. They start paying monthly installments after that term, for
[PeriodLength] in months, for which [PeriodLength] + [InitialPeriod] = [total
scheme length] in months, and [StartDate] + [total scheme length] in months =
[end of scheme] date. The system is capable of detecting when this date has
passed, and warns employees to stop taking monthly payments, (eg cancel the
direct debit). The problem comes however when trying to display payments that
are due to go out since the last time payments were checked,
[LastUpdatePoint].

For example, an employee takes all payments due from 20/11/2007 through to
the current Date(), 25/11/07 (the day of which each payment due relying on
the date of the start date, for example if the start date was 23/6/2007). The
new [LastUpdatePoint] goes into the table as 25/11/07 hence the day it was
performed (independant of any records). So when an employee returns on the
28th, the only records to be displayed are ones that require payment (and
hence not the payment due on the 23rd, but from the 25th-28th).

I need a query to display the schemes that have not passed [end of scheme],
that HAVE reached [StartDate] + [InitialPeriod], and within THAT set of
values, schemes for which the DatePart "days" value of [StartDate] is between
the DatePart date values of [LastUpdatePoint] and Date() respectively.

I know this is a mathematical nightmare, but I'm really stuck as you can
imagine!!

Again thanks for any correspondance!

Edd