sub query

[Jeff Klein]

I need a way to create an expression in my query that shows a
SeriesStartDate based on the StartRecurRange.
The table has chk boxes that represent days of the week. the
StartRecurRange may be on a Monday but the chk box for Friday is checked.
How do I set the SeriesStartDate based on the idea that

SeriesStartDate = StartRecurRange + (Weekday of the checkbox)

Below is the code that does not work...I am inexperienced at subqueries
Please help!


SELECT Meetings.MeetingID, Meetings.StartTime, Meetings.EndTime,
Meetings.StartRecurRange, Meetings.EndRecurRange, Meetings.ClientID,
Meetings.EmployeeID, Meetings.RecurEveryweeks,

( dateadd(StartRecurRange + 0) WHERE [Sunday] = -1 OR
dateadd(StartRecurRange + 1) WHERE [Monday] = -1) OR
dateadd(StartRecurRange + 2) WHERE [Tuesday] = -1) OR
dateadd(StartRecurRange + 3) WHERE [Wednesday] = -1) OR
dateadd(StartRecurRange + 4) WHERE [Thursday] = -1) OR
dateadd(StartRecurRange + 5) WHERE [Friday] = -1) OR
dateadd(StartRecurRange + 6) WHERE [Saturday] = -1) as SeriesStartDate

FROM Meetings
WHERE (((Meetings.RecurEveryweeks)=2));

 

[John Spencer (MVP)]

The correct syntax is:

DateAdd("d",N,StartRecurRange)

That is add N "d"ays to StartRecurRange.

So Add zero to six depending on which box is checked (assumes only one check box
can be checked) - ignore Sunday

N= Abs([Monday] + ([Tuesday]*2) + ([Wednesday]*3) + (Thursday*4)...)

Or use the Switch function.
Switch(Sunday,0,Monday,1,Tuesday,2,Wednesday,3,Thursday,4,Friday,5,Saturday,6)

Assembled


DateAdd("d",Switch(Sunday,0,Monday,1,Tuesday,2,Wednesday,3,Thursday,4,Friday,5,Saturday,6),StartRecurRange)
as SeriesStartDate

 

[Jeff]

John,
Thanks (again) for the help!

The correct syntax is:

DateAdd("d",N,StartRecurRange)

That is add N "d"ays to StartRecurRange.

So Add zero to six depending on which box is checked (assumes only one check box
can be checked) - ignore Sunday

N= Abs([Monday] + ([Tuesday]*2) + ([Wednesday]*3) + (Thursday*4)...)

Or use the Switch function.
Switch(Sunday,0,Monday,1,Tuesday,2,Wednesday,3,Thursday,4,Friday,5,Saturday,
6)

Assembled

DateAdd("d",Switch(Sunday,0,Monday,1,Tuesday,2,Wednesday,3,Thursday,4,Friday
,5,Saturday,6),StartRecurRange)
as SeriesStartDate

I need a way to create an expression in my query that shows a
SeriesStartDate based on the StartRecurRange.
The table has chk boxes that represent days of the week. the
StartRecurRange may be on a Monday but the chk box for Friday is checked.
How do I set the SeriesStartDate based on the idea that

SeriesStartDate = StartRecurRange + (Weekday of the checkbox)

Below is the code that does not work...I am inexperienced at subqueries
Please help!

SELECT Meetings.MeetingID, Meetings.StartTime, Meetings.EndTime,
Meetings.StartRecurRange, Meetings.EndRecurRange, Meetings.ClientID,
Meetings.EmployeeID, Meetings.RecurEveryweeks,

( dateadd(StartRecurRange + 0) WHERE [Sunday] = -1 OR
dateadd(StartRecurRange + 1) WHERE [Monday] = -1) OR
dateadd(StartRecurRange + 2) WHERE [Tuesday] = -1) OR
dateadd(StartRecurRange + 3) WHERE [Wednesday] = -1) OR
dateadd(StartRecurRange + 4) WHERE [Thursday] = -1) OR
dateadd(StartRecurRange + 5) WHERE [Friday] = -1) OR
dateadd(StartRecurRange + 6) WHERE [Saturday] = -1) as SeriesStartDate

FROM Meetings
WHERE (((Meetings.RecurEveryweeks)=2));