[AccessD] A2K: Repeat Appointments

Sorry Gustav,
When I add the second w? now I get null recordsets again.
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.

I'm open for suggestions.

Many many thanks

Darren



-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Wednesday, 12 February 2003 1:44 AM
To: Darren Dick
Subject: Re: [AccessD] A2K: Repeat Appointments


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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com