[AccessD] A2K: Repeat Appointments

Hi Darren

> When I add the second w? now I get null recordsets again.

The intervals are those allowed by the DateAdd() and DateDiff()
functions.
This means that in your booking table the intervals must be specified
accordingly - as one of these:

  "h","d","ww","m", or "q"

Anything else will result in blank records.

/gustav
  
> Your code is very elegant and way above me. :-(
> If it breaks, I won't be able to fix it.
> I just don't understand the concepts well enough.

>> Thanks for that - Excellent - Now I am seeing some results.
>> That's the good bit. Now for more bits I don't get :-)

>> If I have a Booking that has a DateStart of 01/01/2003 (1st Jan)
>> and a DateEnd of 31/01/2003 (31st Jan)(We put the day before the month
>> here in Oz)
>> The interval is "w" and the shouldn't I expect the SQL
>> to create an entry for each weekdate (7days) after startDate but not after
>> Endate

>> EG 1/1/2003 (DateStart) and 31/01/2003 (DateStart) Multiplier of "w"
>> should return...
>> 01/01/2003
>> 08/01/2003
>> 15/01/2003
>> 22/01/2003

>> Shouldn't it??

> Yes, and 29/01/2003.
> That's, of course (!), why you must change the "w" to "ww":

>  SELECT
>    tblBooking.ID,
>    tblBooking.Interval,
>    tblBooking.DateStart,
>    tblBooking.DateEnd,
>    DateAdd([Interval],[Factor],[DateStart]) AS DateRepeat
>  FROM
>    tblBooking,
>    tbzMultiply
>  WHERE
>    (tbzMultiply.Factor <= DateDiff([Interval],[DateStart],[DateEnd]))
>      AND
>    (tblBooking.Interval IN ("h","d","ww","m","q"))
>  ORDER BY
>    tblBooking.ID,
>    tbzMultiply.Factor;

> Also:

>> Did you add to your table the field Interval (text) which for every
>> record must contain an "h","d","ww","m", or "q"?