[AccessD] A2K: Repeat Appointments

Hi Gustav
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??

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 12:29 AM
To: Darren Dick
Subject: Re: [AccessD] A2K: Repeat Appointments


Hi Darren

> When I copy and paste the SQL then run the query I get an output similar
> to returned recordset with no records. i.e. just column headers no
records.

> How should I be applying this??

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

/gustav


> Oops - a closing parenthesis was cut off. Sorry.
> Try this:

> <SQL>

> 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","w","m","q"))
> ORDER BY
>   tblBooking.ID,
>   tbzMultiply.Factor;

> </SQL>

> /gustav


>> Hi Gustav
>> Many thanks for the reply.
>> This is over my head but I can certainly see the potential.
>> I have modified my tables as per your SQL but when I copy and
>> paste the SQL into the QueryDesignGrid I am getting errors related to
>> the IN function

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