Big brain problem!

  • Thread starter Thread starter Jeff Klein
  • Start date Start date
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);
 
Hi Jeff,

If "show me" means in a report,
then why not keep it simple and
just create a hidden textbox in Detail
section called, say, "txtCnt" whose
Control Source
= 1
and Running Sum = Yes.

In code behind report, hide or display
Detail (or whatever "section" results and txtCnt
will be in, i.e., maybe results are in a
group header or footer instead) by applying
MOD test there on txtCnt.

This would allow you to always start
showing the "first" record, no matter
if its "weekday" was odd or even.

Just an idea.

Gary Walter
 
Gary, Thanks for the reply..I have ,with your help, found a solution...had
to tweak things a bit but things are good...thanks again for the help
Jeff


Gary Walter said:
Hi Jeff,

If "show me" means in a report,
then why not keep it simple and
just create a hidden textbox in Detail
section called, say, "txtCnt" whose
Control Source
= 1
and Running Sum = Yes.

In code behind report, hide or display
Detail (or whatever "section" results and txtCnt
will be in, i.e., maybe results are in a
group header or footer instead) by applying
MOD test there on txtCnt.

This would allow you to always start
showing the "first" record, no matter
if its "weekday" was odd or even.

Just an idea.

Gary Walter

Jeff Klein said:
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);
Click to expand...
Click to expand...
 
Back
Top