J
Jeff Klein
I have a table with check boxes for every day of the week. My query shows
all dates (between MonthStartDate and MonthEndDate) if its respective
checkbox is marked. The query is a "cartesian cross product" and it uses
the table Iota (number 0-31) to generate all dates for the month. OK
....here is the problem that I am struggling with. I would like to modify
the query to show me "Every other" of the marked days. Below is the query
that shows selected days of the month. Somebody please help, I need to
complete by Monday.
SELECT qry_MeetingsBetweenDates.MeetingID, Meetings.ClientID,
Meetings.EmployeeID, qry_MeetingsBetweenDates.StartRecurRange,
qry_MeetingsBetweenDates.ActualEndRecurRange,
DateAdd("d",txb_MonthStartDate,[Iota]-1) AS MeetingDate,
Weekday(DateAdd("d",txb_MonthStartDate,[Iota]-1)) AS DayofWeek,
Meetings.MeetingType, Meetings.StartTime, Meetings.EndTime,
(DateAdd("d",[txb_MonthStartDate],[Iota]-1)-[Meetings].[StartRecurRange])
Mod 2 AS mod, Meetings.RecurEveryweeks AS Expr1
FROM Iota, Meetings INNER JOIN qry_MeetingsBetweenDates ON
Meetings.MeetingID = qry_MeetingsBetweenDates.MeetingID
WHERE (((Meetings.MeetingType)="weekly")
AND ((Meetings.RecurEveryweeks)=2)
AND ((DateAdd("d",[txb_MonthStartDate],[Iota]-1))>=[txb_MonthStartDate]
And (DateAdd("d",[txb_MonthStartDate],[Iota]-1))<=[txb_MonthEndDate]
And
(DateAdd("d",[txb_MonthStartDate],[Iota]-1))>=[qry_MeetingsBetweenDates].[St
artRecurRange]
And
(DateAdd("d",[txb_MonthStartDate],[Iota]-1))<=nz([Meetings].[endrecurrange],
#12/31/9999#))
AND ((Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Sunday]*1 AND
Or (Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Monday]*2
Or (Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Tuesday]*3
Or (Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Wednesday]*4
Or (Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Thursday]*5
Or (Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Friday]*6
Or (Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Saturday]*7))
ORDER BY qry_MeetingsBetweenDates.MeetingID,
DateAdd("d",txb_MonthStartDate,[Iota]-1);
all dates (between MonthStartDate and MonthEndDate) if its respective
checkbox is marked. The query is a "cartesian cross product" and it uses
the table Iota (number 0-31) to generate all dates for the month. OK
....here is the problem that I am struggling with. I would like to modify
the query to show me "Every other" of the marked days. Below is the query
that shows selected days of the month. Somebody please help, I need to
complete by Monday.
SELECT qry_MeetingsBetweenDates.MeetingID, Meetings.ClientID,
Meetings.EmployeeID, qry_MeetingsBetweenDates.StartRecurRange,
qry_MeetingsBetweenDates.ActualEndRecurRange,
DateAdd("d",txb_MonthStartDate,[Iota]-1) AS MeetingDate,
Weekday(DateAdd("d",txb_MonthStartDate,[Iota]-1)) AS DayofWeek,
Meetings.MeetingType, Meetings.StartTime, Meetings.EndTime,
(DateAdd("d",[txb_MonthStartDate],[Iota]-1)-[Meetings].[StartRecurRange])
Mod 2 AS mod, Meetings.RecurEveryweeks AS Expr1
FROM Iota, Meetings INNER JOIN qry_MeetingsBetweenDates ON
Meetings.MeetingID = qry_MeetingsBetweenDates.MeetingID
WHERE (((Meetings.MeetingType)="weekly")
AND ((Meetings.RecurEveryweeks)=2)
AND ((DateAdd("d",[txb_MonthStartDate],[Iota]-1))>=[txb_MonthStartDate]
And (DateAdd("d",[txb_MonthStartDate],[Iota]-1))<=[txb_MonthEndDate]
And
(DateAdd("d",[txb_MonthStartDate],[Iota]-1))>=[qry_MeetingsBetweenDates].[St
artRecurRange]
And
(DateAdd("d",[txb_MonthStartDate],[Iota]-1))<=nz([Meetings].[endrecurrange],
#12/31/9999#))
AND ((Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Sunday]*1 AND
Or (Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Monday]*2
Or (Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Tuesday]*3
Or (Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Wednesday]*4
Or (Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Thursday]*5
Or (Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Friday]*6
Or (Weekday(DateAdd("d",[txb_MonthStartDate],[Iota]-1)))=-[Saturday]*7))
ORDER BY qry_MeetingsBetweenDates.MeetingID,
DateAdd("d",txb_MonthStartDate,[Iota]-1);