[AccessD] A2K: Repeat Appointments

Gustav Brock gustav at cactus.dk
Tue Feb 11 09:00:07 CST 2003


Hi Darren

> 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"?

/gustav




More information about the AccessD mailing list