query to define current week?

This runs, but does not narrow to current week.

suggestions appreciated!

SELECT lngEid, dtmApptDate, Subject, Appt_ID
FROM qry002
WHERE (dtmApptDate BETWEEN
DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And DateAdd("d", 6,
DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))

re: query to define current week?

What is your "first day of week"? Since you are adding 2 to the WeekDay
result, I'm assuming Sunday is 1 and that you want the work week which
starts on Monday. If today is Wednesday, that would be day 4 (-4 + 2) would
subtract 2 from Wednesday resulting in Monday. In the next equation you then
add 6 to this. Monday is 2 plus 6 would be 8. If you are after the current
work week, then you probably want Friday (6) as your second date, in which
case the 6 below should be a 4.

Sunday - 1
Monday - 2
Tuesday - 3
Wednesday - 4
Thursday - 5
Friday - 6
Saturday - 7

This can be changed by specifying the first day of the week in the WeekDay
function.

--
Wayne Morgan
Microsoft Access MVP


"deko" <dje422@hotmail.com> wrote in message
news:yBxAb.66685$kI2.12965@newssvr25.news.prodigy. com...[color=blue]
> This runs, but does not narrow to current week.
>
> suggestions appreciated!
>
> SELECT lngEid, dtmApptDate, Subject, Appt_ID
> FROM qry002
> WHERE (dtmApptDate BETWEEN
> DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And DateAdd("d", 6,
> DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
>
>[/color]

re: query to define current week?

"deko" <dje422@hotmail.com> wrote in message news:<yBxAb.66685$kI2.12965@newssvr25.news.prodigy .com>...[color=blue]
> This runs, but does not narrow to current week.
>
> suggestions appreciated!
>
> SELECT lngEid, dtmApptDate, Subject, Appt_ID
> FROM qry002
> WHERE (dtmApptDate BETWEEN
> DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And DateAdd("d", 6,
> DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))[/color]

You want it between a date in this week, so you probably want
something like...

WHERE dtmApptDate BETWEEN Date AND DateAdd("d",6,Date)...

(i.e., between today and six days from now). You should be able to
just use DateAdd to get the date range you want... unless I'm not
understanding something here.

re: query to define current week?

Hi and thanks for the reply.

Sunday is first day of the week, Saturday is last day.

Here's what I'm trying to do:

I've imported Outlook Appointments into an Access table -- each record in
the database has a number of these appointments associated it. When the
user clicks a button to show all appointments for a particular record for
the current week, I'm trying to query the table for all appointments that
fall within that range. So, if today is Wednesday, past appointments for
Sunday, Monday and Tuesday would be returned as well as future appointments
for Thursday, Friday and Saturday (and of course any appointments on
Wednesday).

I'm unsure of syntax after the AND statement and thought that be my error??
perhaps I'm going about this all wrong??

Thanks again for your help...

assuming today is wednseday (4)...

SELECT lngEid, dtmApptDate, Subject, Appt_ID
FROM qry002
WHERE (dtmApptDate BETWEEN
DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) 'add 2 to -4 = 2 ??
AND DateAdd("d", 6, DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
'add 6 to 2,4 ??




"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:vYxAb.38994$se4.27540@newssvr31.news.prodigy. com...[color=blue]
> What is your "first day of week"? Since you are adding 2 to the WeekDay
> result, I'm assuming Sunday is 1 and that you want the work week which
> starts on Monday. If today is Wednesday, that would be day 4 (-4 + 2)[/color]
would[color=blue]
> subtract 2 from Wednesday resulting in Monday. In the next equation you[/color]
then[color=blue]
> add 6 to this. Monday is 2 plus 6 would be 8. If you are after the current
> work week, then you probably want Friday (6) as your second date, in which
> case the 6 below should be a 4.
>
> Sunday - 1
> Monday - 2
> Tuesday - 3
> Wednesday - 4
> Thursday - 5
> Friday - 6
> Saturday - 7
>
> This can be changed by specifying the first day of the week in the WeekDay
> function.
>
> --
> Wayne Morgan
> Microsoft Access MVP
>
>
> "deko" <dje422@hotmail.com> wrote in message
> news:yBxAb.66685$kI2.12965@newssvr25.news.prodigy. com...[color=green]
> > This runs, but does not narrow to current week.
> >
> > suggestions appreciated!
> >
> > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > FROM qry002
> > WHERE (dtmApptDate BETWEEN
> > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And DateAdd("d", 6,
> > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> >
> >[/color]
>
>[/color]

re: query to define current week?

I think this is what it would look like in code:

strWd = Weekday(dtmApptDate)
strEw = (7 - intWd)
strBw = (intWd - 1)
If dtmApptDate >= (Date - strBw) And dtmApptDate <= (Date + strEw) Then
MsgBox "This Week"
End If


"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:vYxAb.38994$se4.27540@newssvr31.news.prodigy. com...[color=blue]
> What is your "first day of week"? Since you are adding 2 to the WeekDay
> result, I'm assuming Sunday is 1 and that you want the work week which
> starts on Monday. If today is Wednesday, that would be day 4 (-4 + 2)[/color]
would[color=blue]
> subtract 2 from Wednesday resulting in Monday. In the next equation you[/color]
then[color=blue]
> add 6 to this. Monday is 2 plus 6 would be 8. If you are after the current
> work week, then you probably want Friday (6) as your second date, in which
> case the 6 below should be a 4.
>
> Sunday - 1
> Monday - 2
> Tuesday - 3
> Wednesday - 4
> Thursday - 5
> Friday - 6
> Saturday - 7
>
> This can be changed by specifying the first day of the week in the WeekDay
> function.
>
> --
> Wayne Morgan
> Microsoft Access MVP
>
>
> "deko" <dje422@hotmail.com> wrote in message
> news:yBxAb.66685$kI2.12965@newssvr25.news.prodigy. com...[color=green]
> > This runs, but does not narrow to current week.
> >
> > suggestions appreciated!
> >
> > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > FROM qry002
> > WHERE (dtmApptDate BETWEEN
> > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And DateAdd("d", 6,
> > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> >
> >[/color]
>
>[/color]

re: query to define current week?

Ok, you were close. Try

WHERE (dtmApptDate BETWEEN
DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And DateAdd("d", 6,
DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate])))
[color=blue]
>add 2 to -4 = 2 ??[/color]
No, add 2 to -4 is -2. Wednesday - 2 is Monday, not Sunday.

--
Wayne Morgan
Microsoft Access MVP


"deko" <dje422@hotmail.com> wrote in message
news:dyDAb.66781$Je2.5936@newssvr25.news.prodigy.c om...[color=blue]
> Hi and thanks for the reply.
>
> Sunday is first day of the week, Saturday is last day.
>
> Here's what I'm trying to do:
>
> I've imported Outlook Appointments into an Access table -- each record in
> the database has a number of these appointments associated it. When the
> user clicks a button to show all appointments for a particular record for
> the current week, I'm trying to query the table for all appointments that
> fall within that range. So, if today is Wednesday, past appointments for
> Sunday, Monday and Tuesday would be returned as well as future[/color]
appointments[color=blue]
> for Thursday, Friday and Saturday (and of course any appointments on
> Wednesday).
>
> I'm unsure of syntax after the AND statement and thought that be my[/color]
error??[color=blue]
> perhaps I'm going about this all wrong??
>
> Thanks again for your help...
>
> assuming today is wednseday (4)...
>
> SELECT lngEid, dtmApptDate, Subject, Appt_ID
> FROM qry002
> WHERE (dtmApptDate BETWEEN
> DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) 'add 2 to -4 = 2 ??
> AND DateAdd("d", 6,[/color]
DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))[color=blue]
> 'add 6 to 2,4 ??
>
>
>
>
> "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
message[color=blue]
> news:vYxAb.38994$se4.27540@newssvr31.news.prodigy. com...[color=green]
> > What is your "first day of week"? Since you are adding 2 to the WeekDay
> > result, I'm assuming Sunday is 1 and that you want the work week which
> > starts on Monday. If today is Wednesday, that would be day 4 (-4 + 2)[/color]
> would[color=green]
> > subtract 2 from Wednesday resulting in Monday. In the next equation you[/color]
> then[color=green]
> > add 6 to this. Monday is 2 plus 6 would be 8. If you are after the[/color][/color]
current[color=blue][color=green]
> > work week, then you probably want Friday (6) as your second date, in[/color][/color]
which[color=blue][color=green]
> > case the 6 below should be a 4.
> >
> > Sunday - 1
> > Monday - 2
> > Tuesday - 3
> > Wednesday - 4
> > Thursday - 5
> > Friday - 6
> > Saturday - 7
> >
> > This can be changed by specifying the first day of the week in the[/color][/color]
WeekDay[color=blue][color=green]
> > function.
> >
> > --
> > Wayne Morgan
> > Microsoft Access MVP
> >
> >
> > "deko" <dje422@hotmail.com> wrote in message
> > news:yBxAb.66685$kI2.12965@newssvr25.news.prodigy. com...[color=darkred]
> > > This runs, but does not narrow to current week.
> > >
> > > suggestions appreciated!
> > >
> > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > FROM qry002
> > > WHERE (dtmApptDate BETWEEN
> > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And DateAdd("d",[/color][/color][/color]
6,[color=blue][color=green][color=darkred]
> > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: query to define current week?

That seems to do it, except I think I need this:

AND (dtmApptDate BETWEEN Date() -6 And Date() +6);

For example:

SELECT lngEid, dtmApptDate, Subject, Appt_ID
FROM qry002
WHERE (dtmApptDate BETWEEN
DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And DateAdd("d", 6,
DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate]))) AND (dtmApptDate
BETWEEN Date() -6 And Date() +6);

Or, is there a better way to narrow the range??

Thanks again! I appreciate the help!!

"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:MbNAb.35026$2z.28673@newssvr32.news.prodigy.c om...[color=blue]
> Ok, you were close. Try
>
> WHERE (dtmApptDate BETWEEN
> DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And DateAdd("d", 6,
> DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate])))
>[color=green]
> >add 2 to -4 = 2 ??[/color]
> No, add 2 to -4 is -2. Wednesday - 2 is Monday, not Sunday.
>
> --
> Wayne Morgan
> Microsoft Access MVP
>
>
> "deko" <dje422@hotmail.com> wrote in message
> news:dyDAb.66781$Je2.5936@newssvr25.news.prodigy.c om...[color=green]
> > Hi and thanks for the reply.
> >
> > Sunday is first day of the week, Saturday is last day.
> >
> > Here's what I'm trying to do:
> >
> > I've imported Outlook Appointments into an Access table -- each record[/color][/color]
in[color=blue][color=green]
> > the database has a number of these appointments associated it. When the
> > user clicks a button to show all appointments for a particular record[/color][/color]
for[color=blue][color=green]
> > the current week, I'm trying to query the table for all appointments[/color][/color]
that[color=blue][color=green]
> > fall within that range. So, if today is Wednesday, past appointments[/color][/color]
for[color=blue][color=green]
> > Sunday, Monday and Tuesday would be returned as well as future[/color]
> appointments[color=green]
> > for Thursday, Friday and Saturday (and of course any appointments on
> > Wednesday).
> >
> > I'm unsure of syntax after the AND statement and thought that be my[/color]
> error??[color=green]
> > perhaps I'm going about this all wrong??
> >
> > Thanks again for your help...
> >
> > assuming today is wednseday (4)...
> >
> > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > FROM qry002
> > WHERE (dtmApptDate BETWEEN
> > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) 'add 2 to -4 = 2[/color][/color]
??[color=blue][color=green]
> > AND DateAdd("d", 6,[/color]
> DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))[color=green]
> > 'add 6 to 2,4 ??
> >
> >
> >
> >
> > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
> message[color=green]
> > news:vYxAb.38994$se4.27540@newssvr31.news.prodigy. com...[color=darkred]
> > > What is your "first day of week"? Since you are adding 2 to the[/color][/color][/color]
WeekDay[color=blue][color=green][color=darkred]
> > > result, I'm assuming Sunday is 1 and that you want the work week which
> > > starts on Monday. If today is Wednesday, that would be day 4 (-4 + 2)[/color]
> > would[color=darkred]
> > > subtract 2 from Wednesday resulting in Monday. In the next equation[/color][/color][/color]
you[color=blue][color=green]
> > then[color=darkred]
> > > add 6 to this. Monday is 2 plus 6 would be 8. If you are after the[/color][/color]
> current[color=green][color=darkred]
> > > work week, then you probably want Friday (6) as your second date, in[/color][/color]
> which[color=green][color=darkred]
> > > case the 6 below should be a 4.
> > >
> > > Sunday - 1
> > > Monday - 2
> > > Tuesday - 3
> > > Wednesday - 4
> > > Thursday - 5
> > > Friday - 6
> > > Saturday - 7
> > >
> > > This can be changed by specifying the first day of the week in the[/color][/color]
> WeekDay[color=green][color=darkred]
> > > function.
> > >
> > > --
> > > Wayne Morgan
> > > Microsoft Access MVP
> > >
> > >
> > > "deko" <dje422@hotmail.com> wrote in message
> > > news:yBxAb.66685$kI2.12965@newssvr25.news.prodigy. com...
> > > > This runs, but does not narrow to current week.
> > > >
> > > > suggestions appreciated!
> > > >
> > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > FROM qry002
> > > > WHERE (dtmApptDate BETWEEN
> > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And[/color][/color][/color]
DateAdd("d",[color=blue]
> 6,[color=green][color=darkred]
> > > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: query to define current week?

> AND (dtmApptDate[color=blue]
> BETWEEN Date() -6 And Date() +6);[/color]

The new part above would give you +/- 6 days from the current date. You
indicated that you wanted to limit it even further to the current calendar
week. Limiting it to the current calendar week is even more restrictive than
this new part and therefore the new part will never come into play. The most
you could be off within one calendar week is 6 days (if today is Sunday then
Saturday is 6 days away) so staying within the calendar week WILL stay
within 6 days.


--
Wayne Morgan
Microsoft Access MVP


"deko" <dje422@hotmail.com> wrote in message
news:2WNAb.66920$Lz3.5060@newssvr25.news.prodigy.c om...[color=blue]
> That seems to do it, except I think I need this:
>
> AND (dtmApptDate BETWEEN Date() -6 And Date() +6);
>
> For example:
>
> SELECT lngEid, dtmApptDate, Subject, Appt_ID
> FROM qry002
> WHERE (dtmApptDate BETWEEN
> DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And DateAdd("d", 6,
> DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate]))) AND (dtmApptDate
> BETWEEN Date() -6 And Date() +6);
>
> Or, is there a better way to narrow the range??
>
> Thanks again! I appreciate the help!!
>
> "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
message[color=blue]
> news:MbNAb.35026$2z.28673@newssvr32.news.prodigy.c om...[color=green]
> > Ok, you were close. Try
> >
> > WHERE (dtmApptDate BETWEEN
> > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And DateAdd("d", 6,
> > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate])))
> >[color=darkred]
> > >add 2 to -4 = 2 ??[/color]
> > No, add 2 to -4 is -2. Wednesday - 2 is Monday, not Sunday.
> >
> > --
> > Wayne Morgan
> > Microsoft Access MVP
> >
> >
> > "deko" <dje422@hotmail.com> wrote in message
> > news:dyDAb.66781$Je2.5936@newssvr25.news.prodigy.c om...[color=darkred]
> > > Hi and thanks for the reply.
> > >
> > > Sunday is first day of the week, Saturday is last day.
> > >
> > > Here's what I'm trying to do:
> > >
> > > I've imported Outlook Appointments into an Access table -- each record[/color][/color]
> in[color=green][color=darkred]
> > > the database has a number of these appointments associated it. When[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > user clicks a button to show all appointments for a particular record[/color][/color]
> for[color=green][color=darkred]
> > > the current week, I'm trying to query the table for all appointments[/color][/color]
> that[color=green][color=darkred]
> > > fall within that range. So, if today is Wednesday, past appointments[/color][/color]
> for[color=green][color=darkred]
> > > Sunday, Monday and Tuesday would be returned as well as future[/color]
> > appointments[color=darkred]
> > > for Thursday, Friday and Saturday (and of course any appointments on
> > > Wednesday).
> > >
> > > I'm unsure of syntax after the AND statement and thought that be my[/color]
> > error??[color=darkred]
> > > perhaps I'm going about this all wrong??
> > >
> > > Thanks again for your help...
> > >
> > > assuming today is wednseday (4)...
> > >
> > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > FROM qry002
> > > WHERE (dtmApptDate BETWEEN
> > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) 'add 2 to -4 = 2[/color][/color]
> ??[color=green][color=darkred]
> > > AND DateAdd("d", 6,[/color]
> > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))[color=darkred]
> > > 'add 6 to 2,4 ??
> > >
> > >
> > >
> > >
> > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
> > message[color=darkred]
> > > news:vYxAb.38994$se4.27540@newssvr31.news.prodigy. com...
> > > > What is your "first day of week"? Since you are adding 2 to the[/color][/color]
> WeekDay[color=green][color=darkred]
> > > > result, I'm assuming Sunday is 1 and that you want the work week[/color][/color][/color]
which[color=blue][color=green][color=darkred]
> > > > starts on Monday. If today is Wednesday, that would be day 4 (-4 +[/color][/color][/color]
2)[color=blue][color=green][color=darkred]
> > > would
> > > > subtract 2 from Wednesday resulting in Monday. In the next equation[/color][/color]
> you[color=green][color=darkred]
> > > then
> > > > add 6 to this. Monday is 2 plus 6 would be 8. If you are after the[/color]
> > current[color=darkred]
> > > > work week, then you probably want Friday (6) as your second date, in[/color]
> > which[color=darkred]
> > > > case the 6 below should be a 4.
> > > >
> > > > Sunday - 1
> > > > Monday - 2
> > > > Tuesday - 3
> > > > Wednesday - 4
> > > > Thursday - 5
> > > > Friday - 6
> > > > Saturday - 7
> > > >
> > > > This can be changed by specifying the first day of the week in the[/color]
> > WeekDay[color=darkred]
> > > > function.
> > > >
> > > > --
> > > > Wayne Morgan
> > > > Microsoft Access MVP
> > > >
> > > >
> > > > "deko" <dje422@hotmail.com> wrote in message
> > > > news:yBxAb.66685$kI2.12965@newssvr25.news.prodigy. com...
> > > > > This runs, but does not narrow to current week.
> > > > >
> > > > > suggestions appreciated!
> > > > >
> > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > FROM qry002
> > > > > WHERE (dtmApptDate BETWEEN
> > > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And[/color][/color]
> DateAdd("d",[color=green]
> > 6,[color=darkred]
> > > > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: query to define current week?

10-4

The reason I thought I needed this is because the range did not seem to
narrow properly... but I think I've got some other problem that is
preventing me from getting the results I'm looking for.

developing...


"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:EhOAb.35038$PM.25970@newssvr32.news.prodigy.c om...[color=blue][color=green]
> > AND (dtmApptDate
> > BETWEEN Date() -6 And Date() +6);[/color]
>
> The new part above would give you +/- 6 days from the current date. You
> indicated that you wanted to limit it even further to the current calendar
> week. Limiting it to the current calendar week is even more restrictive[/color]
than[color=blue]
> this new part and therefore the new part will never come into play. The[/color]
most[color=blue]
> you could be off within one calendar week is 6 days (if today is Sunday[/color]
then[color=blue]
> Saturday is 6 days away) so staying within the calendar week WILL stay
> within 6 days.
>
>
> --
> Wayne Morgan
> Microsoft Access MVP
>
>
> "deko" <dje422@hotmail.com> wrote in message
> news:2WNAb.66920$Lz3.5060@newssvr25.news.prodigy.c om...[color=green]
> > That seems to do it, except I think I need this:
> >
> > AND (dtmApptDate BETWEEN Date() -6 And Date() +6);
> >
> > For example:
> >
> > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > FROM qry002
> > WHERE (dtmApptDate BETWEEN
> > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And DateAdd("d", 6,
> > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate]))) AND (dtmApptDate
> > BETWEEN Date() -6 And Date() +6);
> >
> > Or, is there a better way to narrow the range??
> >
> > Thanks again! I appreciate the help!!
> >
> > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
> message[color=green]
> > news:MbNAb.35026$2z.28673@newssvr32.news.prodigy.c om...[color=darkred]
> > > Ok, you were close. Try
> > >
> > > WHERE (dtmApptDate BETWEEN
> > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And DateAdd("d",[/color][/color][/color]
6,[color=blue][color=green][color=darkred]
> > > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate])))
> > >
> > > >add 2 to -4 = 2 ??
> > > No, add 2 to -4 is -2. Wednesday - 2 is Monday, not Sunday.
> > >
> > > --
> > > Wayne Morgan
> > > Microsoft Access MVP
> > >
> > >
> > > "deko" <dje422@hotmail.com> wrote in message
> > > news:dyDAb.66781$Je2.5936@newssvr25.news.prodigy.c om...
> > > > Hi and thanks for the reply.
> > > >
> > > > Sunday is first day of the week, Saturday is last day.
> > > >
> > > > Here's what I'm trying to do:
> > > >
> > > > I've imported Outlook Appointments into an Access table -- each[/color][/color][/color]
record[color=blue][color=green]
> > in[color=darkred]
> > > > the database has a number of these appointments associated it. When[/color][/color]
> the[color=green][color=darkred]
> > > > user clicks a button to show all appointments for a particular[/color][/color][/color]
record[color=blue][color=green]
> > for[color=darkred]
> > > > the current week, I'm trying to query the table for all appointments[/color]
> > that[color=darkred]
> > > > fall within that range. So, if today is Wednesday, past[/color][/color][/color]
appointments[color=blue][color=green]
> > for[color=darkred]
> > > > Sunday, Monday and Tuesday would be returned as well as future
> > > appointments
> > > > for Thursday, Friday and Saturday (and of course any appointments on
> > > > Wednesday).
> > > >
> > > > I'm unsure of syntax after the AND statement and thought that be my
> > > error??
> > > > perhaps I'm going about this all wrong??
> > > >
> > > > Thanks again for your help...
> > > >
> > > > assuming today is wednseday (4)...
> > > >
> > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > FROM qry002
> > > > WHERE (dtmApptDate BETWEEN
> > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) 'add 2 to -4 =[/color][/color][/color]
2[color=blue][color=green]
> > ??[color=darkred]
> > > > AND DateAdd("d", 6,
> > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > 'add 6 to 2,4 ??
> > > >
> > > >
> > > >
> > > >
> > > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in
> > > message
> > > > news:vYxAb.38994$se4.27540@newssvr31.news.prodigy. com...
> > > > > What is your "first day of week"? Since you are adding 2 to the[/color]
> > WeekDay[color=darkred]
> > > > > result, I'm assuming Sunday is 1 and that you want the work week[/color][/color]
> which[color=green][color=darkred]
> > > > > starts on Monday. If today is Wednesday, that would be day 4 (-4 +[/color][/color]
> 2)[color=green][color=darkred]
> > > > would
> > > > > subtract 2 from Wednesday resulting in Monday. In the next[/color][/color][/color]
equation[color=blue][color=green]
> > you[color=darkred]
> > > > then
> > > > > add 6 to this. Monday is 2 plus 6 would be 8. If you are after the
> > > current
> > > > > work week, then you probably want Friday (6) as your second date,[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> > > which
> > > > > case the 6 below should be a 4.
> > > > >
> > > > > Sunday - 1
> > > > > Monday - 2
> > > > > Tuesday - 3
> > > > > Wednesday - 4
> > > > > Thursday - 5
> > > > > Friday - 6
> > > > > Saturday - 7
> > > > >
> > > > > This can be changed by specifying the first day of the week in the
> > > WeekDay
> > > > > function.
> > > > >
> > > > > --
> > > > > Wayne Morgan
> > > > > Microsoft Access MVP
> > > > >
> > > > >
> > > > > "deko" <dje422@hotmail.com> wrote in message
> > > > > news:yBxAb.66685$kI2.12965@newssvr25.news.prodigy. com...
> > > > > > This runs, but does not narrow to current week.
> > > > > >
> > > > > > suggestions appreciated!
> > > > > >
> > > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > > FROM qry002
> > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And[/color]
> > DateAdd("d",[color=darkred]
> > > 6,
> > > > > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: query to define current week?

Can't seem to get this to narrow to current week - seems like the entire
WHERE statement is ignored.

SELECT DISTINCT qry900.Entity_ID, qry900.Both, qry900.dtmApptDate
FROM qry900
WHERE ([dtmApptDate] Between
DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And
DateAdd("d",6,DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate])))
ORDER BY qry900.Both

The DateAdd calculations seem to be correct in terms of start/end of week,
but in english, doesn't this read:

where 12/7/2003 is between 1 and 7 ???

am I missing something?

could DISTINCT be screwing things up? there are some entries with the same
Entity_ID and different "Both" and "dtmApptDate" entries - which is okay if
the dates are within the current week, but they are not...

thanks again for the help!!

"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:EhOAb.35038$PM.25970@newssvr32.news.prodigy.c om...[color=blue][color=green]
> > AND (dtmApptDate
> > BETWEEN Date() -6 And Date() +6);[/color]
>
> The new part above would give you +/- 6 days from the current date. You
> indicated that you wanted to limit it even further to the current calendar
> week. Limiting it to the current calendar week is even more restrictive[/color]
than[color=blue]
> this new part and therefore the new part will never come into play. The[/color]
most[color=blue]
> you could be off within one calendar week is 6 days (if today is Sunday[/color]
then[color=blue]
> Saturday is 6 days away) so staying within the calendar week WILL stay
> within 6 days.
>
>
> --
> Wayne Morgan
> Microsoft Access MVP
>
>
> "deko" <dje422@hotmail.com> wrote in message
> news:2WNAb.66920$Lz3.5060@newssvr25.news.prodigy.c om...[color=green]
> > That seems to do it, except I think I need this:
> >
> > AND (dtmApptDate BETWEEN Date() -6 And Date() +6);
> >
> > For example:
> >
> > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > FROM qry002
> > WHERE (dtmApptDate BETWEEN
> > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And DateAdd("d", 6,
> > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate]))) AND (dtmApptDate
> > BETWEEN Date() -6 And Date() +6);
> >
> > Or, is there a better way to narrow the range??
> >
> > Thanks again! I appreciate the help!!
> >
> > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
> message[color=green]
> > news:MbNAb.35026$2z.28673@newssvr32.news.prodigy.c om...[color=darkred]
> > > Ok, you were close. Try
> > >
> > > WHERE (dtmApptDate BETWEEN
> > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And DateAdd("d",[/color][/color][/color]
6,[color=blue][color=green][color=darkred]
> > > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate])))
> > >
> > > >add 2 to -4 = 2 ??
> > > No, add 2 to -4 is -2. Wednesday - 2 is Monday, not Sunday.
> > >
> > > --
> > > Wayne Morgan
> > > Microsoft Access MVP
> > >
> > >
> > > "deko" <dje422@hotmail.com> wrote in message
> > > news:dyDAb.66781$Je2.5936@newssvr25.news.prodigy.c om...
> > > > Hi and thanks for the reply.
> > > >
> > > > Sunday is first day of the week, Saturday is last day.
> > > >
> > > > Here's what I'm trying to do:
> > > >
> > > > I've imported Outlook Appointments into an Access table -- each[/color][/color][/color]
record[color=blue][color=green]
> > in[color=darkred]
> > > > the database has a number of these appointments associated it. When[/color][/color]
> the[color=green][color=darkred]
> > > > user clicks a button to show all appointments for a particular[/color][/color][/color]
record[color=blue][color=green]
> > for[color=darkred]
> > > > the current week, I'm trying to query the table for all appointments[/color]
> > that[color=darkred]
> > > > fall within that range. So, if today is Wednesday, past[/color][/color][/color]
appointments[color=blue][color=green]
> > for[color=darkred]
> > > > Sunday, Monday and Tuesday would be returned as well as future
> > > appointments
> > > > for Thursday, Friday and Saturday (and of course any appointments on
> > > > Wednesday).
> > > >
> > > > I'm unsure of syntax after the AND statement and thought that be my
> > > error??
> > > > perhaps I'm going about this all wrong??
> > > >
> > > > Thanks again for your help...
> > > >
> > > > assuming today is wednseday (4)...
> > > >
> > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > FROM qry002
> > > > WHERE (dtmApptDate BETWEEN
> > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) 'add 2 to -4 =[/color][/color][/color]
2[color=blue][color=green]
> > ??[color=darkred]
> > > > AND DateAdd("d", 6,
> > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > 'add 6 to 2,4 ??
> > > >
> > > >
> > > >
> > > >
> > > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in
> > > message
> > > > news:vYxAb.38994$se4.27540@newssvr31.news.prodigy. com...
> > > > > What is your "first day of week"? Since you are adding 2 to the[/color]
> > WeekDay[color=darkred]
> > > > > result, I'm assuming Sunday is 1 and that you want the work week[/color][/color]
> which[color=green][color=darkred]
> > > > > starts on Monday. If today is Wednesday, that would be day 4 (-4 +[/color][/color]
> 2)[color=green][color=darkred]
> > > > would
> > > > > subtract 2 from Wednesday resulting in Monday. In the next[/color][/color][/color]
equation[color=blue][color=green]
> > you[color=darkred]
> > > > then
> > > > > add 6 to this. Monday is 2 plus 6 would be 8. If you are after the
> > > current
> > > > > work week, then you probably want Friday (6) as your second date,[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> > > which
> > > > > case the 6 below should be a 4.
> > > > >
> > > > > Sunday - 1
> > > > > Monday - 2
> > > > > Tuesday - 3
> > > > > Wednesday - 4
> > > > > Thursday - 5
> > > > > Friday - 6
> > > > > Saturday - 7
> > > > >
> > > > > This can be changed by specifying the first day of the week in the
> > > WeekDay
> > > > > function.
> > > > >
> > > > > --
> > > > > Wayne Morgan
> > > > > Microsoft Access MVP
> > > > >
> > > > >
> > > > > "deko" <dje422@hotmail.com> wrote in message
> > > > > news:yBxAb.66685$kI2.12965@newssvr25.news.prodigy. com...
> > > > > > This runs, but does not narrow to current week.
> > > > > >
> > > > > > suggestions appreciated!
> > > > > >
> > > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > > FROM qry002
> > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And[/color]
> > DateAdd("d",[color=darkred]
> > > 6,
> > > > > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: query to define current week?

I think the problem was including [dtmApptDate] in the SELECT statement

developing...

"deko" <dje422@hotmail.com> wrote in message
news:0yUAb.67042$i06.18731@newssvr25.news.prodigy. com...[color=blue]
> Can't seem to get this to narrow to current week - seems like the entire
> WHERE statement is ignored.
>
> SELECT DISTINCT qry900.Entity_ID, qry900.Both, qry900.dtmApptDate
> FROM qry900
> WHERE ([dtmApptDate] Between
> DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And
> DateAdd("d",6,DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate])))
> ORDER BY qry900.Both
>
> The DateAdd calculations seem to be correct in terms of start/end of week,
> but in english, doesn't this read:
>
> where 12/7/2003 is between 1 and 7 ???
>
> am I missing something?
>
> could DISTINCT be screwing things up? there are some entries with the[/color]
same[color=blue]
> Entity_ID and different "Both" and "dtmApptDate" entries - which is okay[/color]
if[color=blue]
> the dates are within the current week, but they are not...
>
> thanks again for the help!!
>
> "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
message[color=blue]
> news:EhOAb.35038$PM.25970@newssvr32.news.prodigy.c om...[color=green][color=darkred]
> > > AND (dtmApptDate
> > > BETWEEN Date() -6 And Date() +6);[/color]
> >
> > The new part above would give you +/- 6 days from the current date. You
> > indicated that you wanted to limit it even further to the current[/color][/color]
calendar[color=blue][color=green]
> > week. Limiting it to the current calendar week is even more restrictive[/color]
> than[color=green]
> > this new part and therefore the new part will never come into play. The[/color]
> most[color=green]
> > you could be off within one calendar week is 6 days (if today is Sunday[/color]
> then[color=green]
> > Saturday is 6 days away) so staying within the calendar week WILL stay
> > within 6 days.
> >
> >
> > --
> > Wayne Morgan
> > Microsoft Access MVP
> >
> >
> > "deko" <dje422@hotmail.com> wrote in message
> > news:2WNAb.66920$Lz3.5060@newssvr25.news.prodigy.c om...[color=darkred]
> > > That seems to do it, except I think I need this:
> > >
> > > AND (dtmApptDate BETWEEN Date() -6 And Date() +6);
> > >
> > > For example:
> > >
> > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > FROM qry002
> > > WHERE (dtmApptDate BETWEEN
> > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And DateAdd("d",[/color][/color][/color]
6,[color=blue][color=green][color=darkred]
> > > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate]))) AND[/color][/color][/color]
(dtmApptDate[color=blue][color=green][color=darkred]
> > > BETWEEN Date() -6 And Date() +6);
> > >
> > > Or, is there a better way to narrow the range??
> > >
> > > Thanks again! I appreciate the help!!
> > >
> > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
> > message[color=darkred]
> > > news:MbNAb.35026$2z.28673@newssvr32.news.prodigy.c om...
> > > > Ok, you were close. Try
> > > >
> > > > WHERE (dtmApptDate BETWEEN
> > > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And[/color][/color][/color]
DateAdd("d",[color=blue]
> 6,[color=green][color=darkred]
> > > > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate])))
> > > >
> > > > >add 2 to -4 = 2 ??
> > > > No, add 2 to -4 is -2. Wednesday - 2 is Monday, not Sunday.
> > > >
> > > > --
> > > > Wayne Morgan
> > > > Microsoft Access MVP
> > > >
> > > >
> > > > "deko" <dje422@hotmail.com> wrote in message
> > > > news:dyDAb.66781$Je2.5936@newssvr25.news.prodigy.c om...
> > > > > Hi and thanks for the reply.
> > > > >
> > > > > Sunday is first day of the week, Saturday is last day.
> > > > >
> > > > > Here's what I'm trying to do:
> > > > >
> > > > > I've imported Outlook Appointments into an Access table -- each[/color][/color]
> record[color=green][color=darkred]
> > > in
> > > > > the database has a number of these appointments associated it.[/color][/color][/color]
When[color=blue][color=green]
> > the[color=darkred]
> > > > > user clicks a button to show all appointments for a particular[/color][/color]
> record[color=green][color=darkred]
> > > for
> > > > > the current week, I'm trying to query the table for all[/color][/color][/color]
appointments[color=blue][color=green][color=darkred]
> > > that
> > > > > fall within that range. So, if today is Wednesday, past[/color][/color]
> appointments[color=green][color=darkred]
> > > for
> > > > > Sunday, Monday and Tuesday would be returned as well as future
> > > > appointments
> > > > > for Thursday, Friday and Saturday (and of course any appointments[/color][/color][/color]
on[color=blue][color=green][color=darkred]
> > > > > Wednesday).
> > > > >
> > > > > I'm unsure of syntax after the AND statement and thought that be[/color][/color][/color]
my[color=blue][color=green][color=darkred]
> > > > error??
> > > > > perhaps I'm going about this all wrong??
> > > > >
> > > > > Thanks again for your help...
> > > > >
> > > > > assuming today is wednseday (4)...
> > > > >
> > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > FROM qry002
> > > > > WHERE (dtmApptDate BETWEEN
> > > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) 'add 2 to -4[/color][/color][/color]
=[color=blue]
> 2[color=green][color=darkred]
> > > ??
> > > > > AND DateAdd("d", 6,
> > > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > > 'add 6 to 2,4 ??
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> > > > message
> > > > > news:vYxAb.38994$se4.27540@newssvr31.news.prodigy. com...
> > > > > > What is your "first day of week"? Since you are adding 2 to the
> > > WeekDay
> > > > > > result, I'm assuming Sunday is 1 and that you want the work week[/color]
> > which[color=darkred]
> > > > > > starts on Monday. If today is Wednesday, that would be day 4 (-4[/color][/color][/color]
+[color=blue][color=green]
> > 2)[color=darkred]
> > > > > would
> > > > > > subtract 2 from Wednesday resulting in Monday. In the next[/color][/color]
> equation[color=green][color=darkred]
> > > you
> > > > > then
> > > > > > add 6 to this. Monday is 2 plus 6 would be 8. If you are after[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > current
> > > > > > work week, then you probably want Friday (6) as your second[/color][/color][/color]
date,[color=blue]
> in[color=green][color=darkred]
> > > > which
> > > > > > case the 6 below should be a 4.
> > > > > >
> > > > > > Sunday - 1
> > > > > > Monday - 2
> > > > > > Tuesday - 3
> > > > > > Wednesday - 4
> > > > > > Thursday - 5
> > > > > > Friday - 6
> > > > > > Saturday - 7
> > > > > >
> > > > > > This can be changed by specifying the first day of the week in[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > WeekDay
> > > > > > function.
> > > > > >
> > > > > > --
> > > > > > Wayne Morgan
> > > > > > Microsoft Access MVP
> > > > > >
> > > > > >
> > > > > > "deko" <dje422@hotmail.com> wrote in message
> > > > > > news:yBxAb.66685$kI2.12965@newssvr25.news.prodigy. com...
> > > > > > > This runs, but does not narrow to current week.
> > > > > > >
> > > > > > > suggestions appreciated!
> > > > > > >
> > > > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > > > FROM qry002
> > > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And
> > > DateAdd("d",
> > > > 6,
> > > > > > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: query to define current week?

nope... that was not the problem...

"deko" <dje422@hotmail.com> wrote in message
news:8EUAb.67045$Uf6.9067@newssvr25.news.prodigy.c om...[color=blue]
> I think the problem was including [dtmApptDate] in the SELECT statement
>
> developing...
>
> "deko" <dje422@hotmail.com> wrote in message
> news:0yUAb.67042$i06.18731@newssvr25.news.prodigy. com...[color=green]
> > Can't seem to get this to narrow to current week - seems like the entire
> > WHERE statement is ignored.
> >
> > SELECT DISTINCT qry900.Entity_ID, qry900.Both, qry900.dtmApptDate
> > FROM qry900
> > WHERE ([dtmApptDate] Between
> > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And
> > DateAdd("d",6,DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate])))
> > ORDER BY qry900.Both
> >
> > The DateAdd calculations seem to be correct in terms of start/end of[/color][/color]
week,[color=blue][color=green]
> > but in english, doesn't this read:
> >
> > where 12/7/2003 is between 1 and 7 ???
> >
> > am I missing something?
> >
> > could DISTINCT be screwing things up? there are some entries with the[/color]
> same[color=green]
> > Entity_ID and different "Both" and "dtmApptDate" entries - which is okay[/color]
> if[color=green]
> > the dates are within the current week, but they are not...
> >
> > thanks again for the help!!
> >
> > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
> message[color=green]
> > news:EhOAb.35038$PM.25970@newssvr32.news.prodigy.c om...[color=darkred]
> > > > AND (dtmApptDate
> > > > BETWEEN Date() -6 And Date() +6);
> > >
> > > The new part above would give you +/- 6 days from the current date.[/color][/color][/color]
You[color=blue][color=green][color=darkred]
> > > indicated that you wanted to limit it even further to the current[/color][/color]
> calendar[color=green][color=darkred]
> > > week. Limiting it to the current calendar week is even more[/color][/color][/color]
restrictive[color=blue][color=green]
> > than[color=darkred]
> > > this new part and therefore the new part will never come into play.[/color][/color][/color]
The[color=blue][color=green]
> > most[color=darkred]
> > > you could be off within one calendar week is 6 days (if today is[/color][/color][/color]
Sunday[color=blue][color=green]
> > then[color=darkred]
> > > Saturday is 6 days away) so staying within the calendar week WILL stay
> > > within 6 days.
> > >
> > >
> > > --
> > > Wayne Morgan
> > > Microsoft Access MVP
> > >
> > >
> > > "deko" <dje422@hotmail.com> wrote in message
> > > news:2WNAb.66920$Lz3.5060@newssvr25.news.prodigy.c om...
> > > > That seems to do it, except I think I need this:
> > > >
> > > > AND (dtmApptDate BETWEEN Date() -6 And Date() +6);
> > > >
> > > > For example:
> > > >
> > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > FROM qry002
> > > > WHERE (dtmApptDate BETWEEN
> > > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And[/color][/color][/color]
DateAdd("d",[color=blue]
> 6,[color=green][color=darkred]
> > > > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate]))) AND[/color][/color]
> (dtmApptDate[color=green][color=darkred]
> > > > BETWEEN Date() -6 And Date() +6);
> > > >
> > > > Or, is there a better way to narrow the range??
> > > >
> > > > Thanks again! I appreciate the help!!
> > > >
> > > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in
> > > message
> > > > news:MbNAb.35026$2z.28673@newssvr32.news.prodigy.c om...
> > > > > Ok, you were close. Try
> > > > >
> > > > > WHERE (dtmApptDate BETWEEN
> > > > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And[/color][/color]
> DateAdd("d",[color=green]
> > 6,[color=darkred]
> > > > > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate])))
> > > > >
> > > > > >add 2 to -4 = 2 ??
> > > > > No, add 2 to -4 is -2. Wednesday - 2 is Monday, not Sunday.
> > > > >
> > > > > --
> > > > > Wayne Morgan
> > > > > Microsoft Access MVP
> > > > >
> > > > >
> > > > > "deko" <dje422@hotmail.com> wrote in message
> > > > > news:dyDAb.66781$Je2.5936@newssvr25.news.prodigy.c om...
> > > > > > Hi and thanks for the reply.
> > > > > >
> > > > > > Sunday is first day of the week, Saturday is last day.
> > > > > >
> > > > > > Here's what I'm trying to do:
> > > > > >
> > > > > > I've imported Outlook Appointments into an Access table -- each[/color]
> > record[color=darkred]
> > > > in
> > > > > > the database has a number of these appointments associated it.[/color][/color]
> When[color=green][color=darkred]
> > > the
> > > > > > user clicks a button to show all appointments for a particular[/color]
> > record[color=darkred]
> > > > for
> > > > > > the current week, I'm trying to query the table for all[/color][/color]
> appointments[color=green][color=darkred]
> > > > that
> > > > > > fall within that range. So, if today is Wednesday, past[/color]
> > appointments[color=darkred]
> > > > for
> > > > > > Sunday, Monday and Tuesday would be returned as well as future
> > > > > appointments
> > > > > > for Thursday, Friday and Saturday (and of course any[/color][/color][/color]
appointments[color=blue]
> on[color=green][color=darkred]
> > > > > > Wednesday).
> > > > > >
> > > > > > I'm unsure of syntax after the AND statement and thought that be[/color][/color]
> my[color=green][color=darkred]
> > > > > error??
> > > > > > perhaps I'm going about this all wrong??
> > > > > >
> > > > > > Thanks again for your help...
> > > > > >
> > > > > > assuming today is wednseday (4)...
> > > > > >
> > > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > > FROM qry002
> > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) 'add 2[/color][/color][/color]
to -4[color=blue]
> =[color=green]
> > 2[color=darkred]
> > > > ??
> > > > > > AND DateAdd("d", 6,
> > > > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > > > 'add 6 to 2,4 ??
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote[/color][/color]
> in[color=green][color=darkred]
> > > > > message
> > > > > > news:vYxAb.38994$se4.27540@newssvr31.news.prodigy. com...
> > > > > > > What is your "first day of week"? Since you are adding 2 to[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > WeekDay
> > > > > > > result, I'm assuming Sunday is 1 and that you want the work[/color][/color][/color]
week[color=blue][color=green][color=darkred]
> > > which
> > > > > > > starts on Monday. If today is Wednesday, that would be day 4[/color][/color][/color]
(-4[color=blue]
> +[color=green][color=darkred]
> > > 2)
> > > > > > would
> > > > > > > subtract 2 from Wednesday resulting in Monday. In the next[/color]
> > equation[color=darkred]
> > > > you
> > > > > > then
> > > > > > > add 6 to this. Monday is 2 plus 6 would be 8. If you are after[/color][/color]
> the[color=green][color=darkred]
> > > > > current
> > > > > > > work week, then you probably want Friday (6) as your second[/color][/color]
> date,[color=green]
> > in[color=darkred]
> > > > > which
> > > > > > > case the 6 below should be a 4.
> > > > > > >
> > > > > > > Sunday - 1
> > > > > > > Monday - 2
> > > > > > > Tuesday - 3
> > > > > > > Wednesday - 4
> > > > > > > Thursday - 5
> > > > > > > Friday - 6
> > > > > > > Saturday - 7
> > > > > > >
> > > > > > > This can be changed by specifying the first day of the week in[/color][/color]
> the[color=green][color=darkred]
> > > > > WeekDay
> > > > > > > function.
> > > > > > >
> > > > > > > --
> > > > > > > Wayne Morgan
> > > > > > > Microsoft Access MVP
> > > > > > >
> > > > > > >
> > > > > > > "deko" <dje422@hotmail.com> wrote in message
> > > > > > > news:yBxAb.66685$kI2.12965@newssvr25.news.prodigy. com...
> > > > > > > > This runs, but does not narrow to current week.
> > > > > > > >
> > > > > > > > suggestions appreciated!
> > > > > > > >
> > > > > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > > > > FROM qry002
> > > > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And
> > > > DateAdd("d",
> > > > > 6,
> > > > > > > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>
>[/color]

re: query to define current week?

Ah ha!

SELECT [Entity_ID], [Both], [dtmApptDate]
FROM qry900
WHERE ([dtmApptDate]) BETWEEN DateAdd("d", -DatePart("w", Date())+1, Date())
AND DateAdd("d", 6, DateAdd("d", -DatePart("w", Date())+1, Date()));

much better....


"deko" <dje422@hotmail.com> wrote in message
news:JGVAb.67270$%k.1302@newssvr25.news.prodigy.co m...[color=blue]
> nope... that was not the problem...
>
> "deko" <dje422@hotmail.com> wrote in message
> news:8EUAb.67045$Uf6.9067@newssvr25.news.prodigy.c om...[color=green]
> > I think the problem was including [dtmApptDate] in the SELECT statement
> >
> > developing...
> >
> > "deko" <dje422@hotmail.com> wrote in message
> > news:0yUAb.67042$i06.18731@newssvr25.news.prodigy. com...[color=darkred]
> > > Can't seem to get this to narrow to current week - seems like the[/color][/color][/color]
entire[color=blue][color=green][color=darkred]
> > > WHERE statement is ignored.
> > >
> > > SELECT DISTINCT qry900.Entity_ID, qry900.Both, qry900.dtmApptDate
> > > FROM qry900
> > > WHERE ([dtmApptDate] Between
> > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And
> > > DateAdd("d",6,DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate])))
> > > ORDER BY qry900.Both
> > >
> > > The DateAdd calculations seem to be correct in terms of start/end of[/color][/color]
> week,[color=green][color=darkred]
> > > but in english, doesn't this read:
> > >
> > > where 12/7/2003 is between 1 and 7 ???
> > >
> > > am I missing something?
> > >
> > > could DISTINCT be screwing things up? there are some entries with[/color][/color][/color]
the[color=blue][color=green]
> > same[color=darkred]
> > > Entity_ID and different "Both" and "dtmApptDate" entries - which is[/color][/color][/color]
okay[color=blue][color=green]
> > if[color=darkred]
> > > the dates are within the current week, but they are not...
> > >
> > > thanks again for the help!!
> > >
> > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
> > message[color=darkred]
> > > news:EhOAb.35038$PM.25970@newssvr32.news.prodigy.c om...
> > > > > AND (dtmApptDate
> > > > > BETWEEN Date() -6 And Date() +6);
> > > >
> > > > The new part above would give you +/- 6 days from the current date.[/color][/color]
> You[color=green][color=darkred]
> > > > indicated that you wanted to limit it even further to the current[/color]
> > calendar[color=darkred]
> > > > week. Limiting it to the current calendar week is even more[/color][/color]
> restrictive[color=green][color=darkred]
> > > than
> > > > this new part and therefore the new part will never come into play.[/color][/color]
> The[color=green][color=darkred]
> > > most
> > > > you could be off within one calendar week is 6 days (if today is[/color][/color]
> Sunday[color=green][color=darkred]
> > > then
> > > > Saturday is 6 days away) so staying within the calendar week WILL[/color][/color][/color]
stay[color=blue][color=green][color=darkred]
> > > > within 6 days.
> > > >
> > > >
> > > > --
> > > > Wayne Morgan
> > > > Microsoft Access MVP
> > > >
> > > >
> > > > "deko" <dje422@hotmail.com> wrote in message
> > > > news:2WNAb.66920$Lz3.5060@newssvr25.news.prodigy.c om...
> > > > > That seems to do it, except I think I need this:
> > > > >
> > > > > AND (dtmApptDate BETWEEN Date() -6 And Date() +6);
> > > > >
> > > > > For example:
> > > > >
> > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > FROM qry002
> > > > > WHERE (dtmApptDate BETWEEN
> > > > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And[/color][/color]
> DateAdd("d",[color=green]
> > 6,[color=darkred]
> > > > > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate]))) AND[/color]
> > (dtmApptDate[color=darkred]
> > > > > BETWEEN Date() -6 And Date() +6);
> > > > >
> > > > > Or, is there a better way to narrow the range??
> > > > >
> > > > > Thanks again! I appreciate the help!!
> > > > >
> > > > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> > > > message
> > > > > news:MbNAb.35026$2z.28673@newssvr32.news.prodigy.c om...
> > > > > > Ok, you were close. Try
> > > > > >
> > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And[/color]
> > DateAdd("d",[color=darkred]
> > > 6,
> > > > > > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate])))
> > > > > >
> > > > > > >add 2 to -4 = 2 ??
> > > > > > No, add 2 to -4 is -2. Wednesday - 2 is Monday, not Sunday.
> > > > > >
> > > > > > --
> > > > > > Wayne Morgan
> > > > > > Microsoft Access MVP
> > > > > >
> > > > > >
> > > > > > "deko" <dje422@hotmail.com> wrote in message
> > > > > > news:dyDAb.66781$Je2.5936@newssvr25.news.prodigy.c om...
> > > > > > > Hi and thanks for the reply.
> > > > > > >
> > > > > > > Sunday is first day of the week, Saturday is last day.
> > > > > > >
> > > > > > > Here's what I'm trying to do:
> > > > > > >
> > > > > > > I've imported Outlook Appointments into an Access table --[/color][/color][/color]
each[color=blue][color=green][color=darkred]
> > > record
> > > > > in
> > > > > > > the database has a number of these appointments associated it.[/color]
> > When[color=darkred]
> > > > the
> > > > > > > user clicks a button to show all appointments for a particular
> > > record
> > > > > for
> > > > > > > the current week, I'm trying to query the table for all[/color]
> > appointments[color=darkred]
> > > > > that
> > > > > > > fall within that range. So, if today is Wednesday, past
> > > appointments
> > > > > for
> > > > > > > Sunday, Monday and Tuesday would be returned as well as future
> > > > > > appointments
> > > > > > > for Thursday, Friday and Saturday (and of course any[/color][/color]
> appointments[color=green]
> > on[color=darkred]
> > > > > > > Wednesday).
> > > > > > >
> > > > > > > I'm unsure of syntax after the AND statement and thought that[/color][/color][/color]
be[color=blue][color=green]
> > my[color=darkred]
> > > > > > error??
> > > > > > > perhaps I'm going about this all wrong??
> > > > > > >
> > > > > > > Thanks again for your help...
> > > > > > >
> > > > > > > assuming today is wednseday (4)...
> > > > > > >
> > > > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > > > FROM qry002
> > > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) 'add 2[/color][/color]
> to -4[color=green]
> > =[color=darkred]
> > > 2
> > > > > ??
> > > > > > > AND DateAdd("d", 6,
> > > > > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > > > > 'add 6 to 2,4 ??
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com>[/color][/color][/color]
wrote[color=blue][color=green]
> > in[color=darkred]
> > > > > > message
> > > > > > > news:vYxAb.38994$se4.27540@newssvr31.news.prodigy. com...
> > > > > > > > What is your "first day of week"? Since you are adding 2 to[/color][/color]
> the[color=green][color=darkred]
> > > > > WeekDay
> > > > > > > > result, I'm assuming Sunday is 1 and that you want the work[/color][/color]
> week[color=green][color=darkred]
> > > > which
> > > > > > > > starts on Monday. If today is Wednesday, that would be day 4[/color][/color]
> (-4[color=green]
> > +[color=darkred]
> > > > 2)
> > > > > > > would
> > > > > > > > subtract 2 from Wednesday resulting in Monday. In the next
> > > equation
> > > > > you
> > > > > > > then
> > > > > > > > add 6 to this. Monday is 2 plus 6 would be 8. If you are[/color][/color][/color]
after[color=blue][color=green]
> > the[color=darkred]
> > > > > > current
> > > > > > > > work week, then you probably want Friday (6) as your second[/color]
> > date,[color=darkred]
> > > in
> > > > > > which
> > > > > > > > case the 6 below should be a 4.
> > > > > > > >
> > > > > > > > Sunday - 1
> > > > > > > > Monday - 2
> > > > > > > > Tuesday - 3
> > > > > > > > Wednesday - 4
> > > > > > > > Thursday - 5
> > > > > > > > Friday - 6
> > > > > > > > Saturday - 7
> > > > > > > >
> > > > > > > > This can be changed by specifying the first day of the week[/color][/color][/color]
in[color=blue][color=green]
> > the[color=darkred]
> > > > > > WeekDay
> > > > > > > > function.
> > > > > > > >
> > > > > > > > --
> > > > > > > > Wayne Morgan
> > > > > > > > Microsoft Access MVP
> > > > > > > >
> > > > > > > >
> > > > > > > > "deko" <dje422@hotmail.com> wrote in message
> > > > > > > > news:yBxAb.66685$kI2.12965@newssvr25.news.prodigy. com...
> > > > > > > > > This runs, but does not narrow to current week.
> > > > > > > > >
> > > > > > > > > suggestions appreciated!
> > > > > > > > >
> > > > > > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > > > > > FROM qry002
> > > > > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And
> > > > > DateAdd("d",
> > > > > > 6,
> > > > > > > > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >
> >[/color]
>
>[/color]

re: query to define current week?

Deko,

I'm glad you got it going. Sometimes just playing with it is the answer.
Apparently I had misunderstood part of what you were after. Using Date() in
DatePart will return items for the current week, using the field would
return items for the week of the date listed in the field, not the present
week.

You should still be able to use WeekDay(Date()). It should give you the same
result as DatePart("w", Date()), I don't know which, if either, is
"quicker". However, the best answer may be to leave it alone since you have
it working.

--
Wayne Morgan
Microsoft Access MVP


"deko" <dje422@hotmail.com> wrote in message
news:ZqWAb.34715$9%3.20434@newssvr29.news.prodigy. com...[color=blue]
> Ah ha!
>
> SELECT [Entity_ID], [Both], [dtmApptDate]
> FROM qry900
> WHERE ([dtmApptDate]) BETWEEN DateAdd("d", -DatePart("w", Date())+1,[/color]
Date())[color=blue]
> AND DateAdd("d", 6, DateAdd("d", -DatePart("w", Date())+1, Date()));
>
> much better....
>
>
> "deko" <dje422@hotmail.com> wrote in message
> news:JGVAb.67270$%k.1302@newssvr25.news.prodigy.co m...[color=green]
> > nope... that was not the problem...
> >
> > "deko" <dje422@hotmail.com> wrote in message
> > news:8EUAb.67045$Uf6.9067@newssvr25.news.prodigy.c om...[color=darkred]
> > > I think the problem was including [dtmApptDate] in the SELECT[/color][/color][/color]
statement[color=blue][color=green][color=darkred]
> > >
> > > developing...
> > >
> > > "deko" <dje422@hotmail.com> wrote in message
> > > news:0yUAb.67042$i06.18731@newssvr25.news.prodigy. com...
> > > > Can't seem to get this to narrow to current week - seems like the[/color][/color]
> entire[color=green][color=darkred]
> > > > WHERE statement is ignored.
> > > >
> > > > SELECT DISTINCT qry900.Entity_ID, qry900.Both, qry900.dtmApptDate
> > > > FROM qry900
> > > > WHERE ([dtmApptDate] Between
> > > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And
> > > > DateAdd("d",6,DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate])))
> > > > ORDER BY qry900.Both
> > > >
> > > > The DateAdd calculations seem to be correct in terms of start/end of[/color]
> > week,[color=darkred]
> > > > but in english, doesn't this read:
> > > >
> > > > where 12/7/2003 is between 1 and 7 ???
> > > >
> > > > am I missing something?
> > > >
> > > > could DISTINCT be screwing things up? there are some entries with[/color][/color]
> the[color=green][color=darkred]
> > > same
> > > > Entity_ID and different "Both" and "dtmApptDate" entries - which is[/color][/color]
> okay[color=green][color=darkred]
> > > if
> > > > the dates are within the current week, but they are not...
> > > >
> > > > thanks again for the help!!
> > > >
> > > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in
> > > message
> > > > news:EhOAb.35038$PM.25970@newssvr32.news.prodigy.c om...
> > > > > > AND (dtmApptDate
> > > > > > BETWEEN Date() -6 And Date() +6);
> > > > >
> > > > > The new part above would give you +/- 6 days from the current[/color][/color][/color]
date.[color=blue][color=green]
> > You[color=darkred]
> > > > > indicated that you wanted to limit it even further to the current
> > > calendar
> > > > > week. Limiting it to the current calendar week is even more[/color]
> > restrictive[color=darkred]
> > > > than
> > > > > this new part and therefore the new part will never come into[/color][/color][/color]
play.[color=blue][color=green]
> > The[color=darkred]
> > > > most
> > > > > you could be off within one calendar week is 6 days (if today is[/color]
> > Sunday[color=darkred]
> > > > then
> > > > > Saturday is 6 days away) so staying within the calendar week WILL[/color][/color]
> stay[color=green][color=darkred]
> > > > > within 6 days.
> > > > >
> > > > >
> > > > > --
> > > > > Wayne Morgan
> > > > > Microsoft Access MVP
> > > > >
> > > > >
> > > > > "deko" <dje422@hotmail.com> wrote in message
> > > > > news:2WNAb.66920$Lz3.5060@newssvr25.news.prodigy.c om...
> > > > > > That seems to do it, except I think I need this:
> > > > > >
> > > > > > AND (dtmApptDate BETWEEN Date() -6 And Date() +6);
> > > > > >
> > > > > > For example:
> > > > > >
> > > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > > FROM qry002
> > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And[/color]
> > DateAdd("d",[color=darkred]
> > > 6,
> > > > > > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate]))) AND
> > > (dtmApptDate
> > > > > > BETWEEN Date() -6 And Date() +6);
> > > > > >
> > > > > > Or, is there a better way to narrow the range??
> > > > > >
> > > > > > Thanks again! I appreciate the help!!
> > > > > >
> > > > > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote[/color][/color]
> in[color=green][color=darkred]
> > > > > message
> > > > > > news:MbNAb.35026$2z.28673@newssvr32.news.prodigy.c om...
> > > > > > > Ok, you were close. Try
> > > > > > >
> > > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And
> > > DateAdd("d",
> > > > 6,
> > > > > > > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate])))
> > > > > > >
> > > > > > > >add 2 to -4 = 2 ??
> > > > > > > No, add 2 to -4 is -2. Wednesday - 2 is Monday, not Sunday.
> > > > > > >
> > > > > > > --
> > > > > > > Wayne Morgan
> > > > > > > Microsoft Access MVP
> > > > > > >
> > > > > > >
> > > > > > > "deko" <dje422@hotmail.com> wrote in message
> > > > > > > news:dyDAb.66781$Je2.5936@newssvr25.news.prodigy.c om...
> > > > > > > > Hi and thanks for the reply.
> > > > > > > >
> > > > > > > > Sunday is first day of the week, Saturday is last day.
> > > > > > > >
> > > > > > > > Here's what I'm trying to do:
> > > > > > > >
> > > > > > > > I've imported Outlook Appointments into an Access table --[/color][/color]
> each[color=green][color=darkred]
> > > > record
> > > > > > in
> > > > > > > > the database has a number of these appointments associated[/color][/color][/color]
it.[color=blue][color=green][color=darkred]
> > > When
> > > > > the
> > > > > > > > user clicks a button to show all appointments for a[/color][/color][/color]
particular[color=blue][color=green][color=darkred]
> > > > record
> > > > > > for
> > > > > > > > the current week, I'm trying to query the table for all
> > > appointments
> > > > > > that
> > > > > > > > fall within that range. So, if today is Wednesday, past
> > > > appointments
> > > > > > for
> > > > > > > > Sunday, Monday and Tuesday would be returned as well as[/color][/color][/color]
future[color=blue][color=green][color=darkred]
> > > > > > > appointments
> > > > > > > > for Thursday, Friday and Saturday (and of course any[/color]
> > appointments[color=darkred]
> > > on
> > > > > > > > Wednesday).
> > > > > > > >
> > > > > > > > I'm unsure of syntax after the AND statement and thought[/color][/color][/color]
that[color=blue]
> be[color=green][color=darkred]
> > > my
> > > > > > > error??
> > > > > > > > perhaps I'm going about this all wrong??
> > > > > > > >
> > > > > > > > Thanks again for your help...
> > > > > > > >
> > > > > > > > assuming today is wednseday (4)...
> > > > > > > >
> > > > > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > > > > FROM qry002
> > > > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) 'add 2[/color]
> > to -4[color=darkred]
> > > =
> > > > 2
> > > > > > ??
> > > > > > > > AND DateAdd("d", 6,
> > > > > > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > > > > > 'add 6 to 2,4 ??
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com>[/color][/color]
> wrote[color=green][color=darkred]
> > > in
> > > > > > > message
> > > > > > > > news:vYxAb.38994$se4.27540@newssvr31.news.prodigy. com...
> > > > > > > > > What is your "first day of week"? Since you are adding 2[/color][/color][/color]
to[color=blue][color=green]
> > the[color=darkred]
> > > > > > WeekDay
> > > > > > > > > result, I'm assuming Sunday is 1 and that you want the[/color][/color][/color]
work[color=blue][color=green]
> > week[color=darkred]
> > > > > which
> > > > > > > > > starts on Monday. If today is Wednesday, that would be day[/color][/color][/color]
4[color=blue][color=green]
> > (-4[color=darkred]
> > > +
> > > > > 2)
> > > > > > > > would
> > > > > > > > > subtract 2 from Wednesday resulting in Monday. In the next
> > > > equation
> > > > > > you
> > > > > > > > then
> > > > > > > > > add 6 to this. Monday is 2 plus 6 would be 8. If you are[/color][/color]
> after[color=green][color=darkred]
> > > the
> > > > > > > current
> > > > > > > > > work week, then you probably want Friday (6) as your[/color][/color][/color]
second[color=blue][color=green][color=darkred]
> > > date,
> > > > in
> > > > > > > which
> > > > > > > > > case the 6 below should be a 4.
> > > > > > > > >
> > > > > > > > > Sunday - 1
> > > > > > > > > Monday - 2
> > > > > > > > > Tuesday - 3
> > > > > > > > > Wednesday - 4
> > > > > > > > > Thursday - 5
> > > > > > > > > Friday - 6
> > > > > > > > > Saturday - 7
> > > > > > > > >
> > > > > > > > > This can be changed by specifying the first day of the wee[/color][/color][/color]
k[color=blue]
> in[color=green][color=darkred]
> > > the
> > > > > > > WeekDay
> > > > > > > > > function.
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Wayne Morgan
> > > > > > > > > Microsoft Access MVP
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "deko" <dje422@hotmail.com> wrote in message
> > > > > > > > > news:yBxAb.66685$kI2.12965@newssvr25.news.prodigy. com...
> > > > > > > > > > This runs, but does not narrow to current week.
> > > > > > > > > >
> > > > > > > > > > suggestions appreciated!
> > > > > > > > > >
> > > > > > > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > > > > > > FROM qry002
> > > > > > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > > > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And
> > > > > > DateAdd("d",
> > > > > > > 6,
> > > > > > > > > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: query to define current week?

10-4 and thanks again for your help

"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:sJ1Bb.4337$El7.3102@newssvr33.news.prodigy.co m...[color=blue]
> Deko,
>
> I'm glad you got it going. Sometimes just playing with it is the answer.
> Apparently I had misunderstood part of what you were after. Using Date()[/color]
in[color=blue]
> DatePart will return items for the current week, using the field would
> return items for the week of the date listed in the field, not the present
> week.
>
> You should still be able to use WeekDay(Date()). It should give you the[/color]
same[color=blue]
> result as DatePart("w", Date()), I don't know which, if either, is
> "quicker". However, the best answer may be to leave it alone since you[/color]
have[color=blue]
> it working.
>
> --
> Wayne Morgan
> Microsoft Access MVP
>
>
> "deko" <dje422@hotmail.com> wrote in message
> news:ZqWAb.34715$9%3.20434@newssvr29.news.prodigy. com...[color=green]
> > Ah ha!
> >
> > SELECT [Entity_ID], [Both], [dtmApptDate]
> > FROM qry900
> > WHERE ([dtmApptDate]) BETWEEN DateAdd("d", -DatePart("w", Date())+1,[/color]
> Date())[color=green]
> > AND DateAdd("d", 6, DateAdd("d", -DatePart("w", Date())+1, Date()));
> >
> > much better....
> >
> >
> > "deko" <dje422@hotmail.com> wrote in message
> > news:JGVAb.67270$%k.1302@newssvr25.news.prodigy.co m...[color=darkred]
> > > nope... that was not the problem...
> > >
> > > "deko" <dje422@hotmail.com> wrote in message
> > > news:8EUAb.67045$Uf6.9067@newssvr25.news.prodigy.c om...
> > > > I think the problem was including [dtmApptDate] in the SELECT[/color][/color]
> statement[color=green][color=darkred]
> > > >
> > > > developing...
> > > >
> > > > "deko" <dje422@hotmail.com> wrote in message
> > > > news:0yUAb.67042$i06.18731@newssvr25.news.prodigy. com...
> > > > > Can't seem to get this to narrow to current week - seems like the[/color]
> > entire[color=darkred]
> > > > > WHERE statement is ignored.
> > > > >
> > > > > SELECT DISTINCT qry900.Entity_ID, qry900.Both, qry900.dtmApptDate
> > > > > FROM qry900
> > > > > WHERE ([dtmApptDate] Between
> > > > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And
> > > > >[/color][/color][/color]
DateAdd("d",6,DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate])))[color=blue][color=green][color=darkred]
> > > > > ORDER BY qry900.Both
> > > > >
> > > > > The DateAdd calculations seem to be correct in terms of start/end[/color][/color][/color]
of[color=blue][color=green][color=darkred]
> > > week,
> > > > > but in english, doesn't this read:
> > > > >
> > > > > where 12/7/2003 is between 1 and 7 ???
> > > > >
> > > > > am I missing something?
> > > > >
> > > > > could DISTINCT be screwing things up? there are some entries[/color][/color][/color]
with[color=blue][color=green]
> > the[color=darkred]
> > > > same
> > > > > Entity_ID and different "Both" and "dtmApptDate" entries - which[/color][/color][/color]
is[color=blue][color=green]
> > okay[color=darkred]
> > > > if
> > > > > the dates are within the current week, but they are not...
> > > > >
> > > > > thanks again for the help!!
> > > > >
> > > > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> > > > message
> > > > > news:EhOAb.35038$PM.25970@newssvr32.news.prodigy.c om...
> > > > > > > AND (dtmApptDate
> > > > > > > BETWEEN Date() -6 And Date() +6);
> > > > > >
> > > > > > The new part above would give you +/- 6 days from the current[/color][/color]
> date.[color=green][color=darkred]
> > > You
> > > > > > indicated that you wanted to limit it even further to the[/color][/color][/color]
current[color=blue][color=green][color=darkred]
> > > > calendar
> > > > > > week. Limiting it to the current calendar week is even more
> > > restrictive
> > > > > than
> > > > > > this new part and therefore the new part will never come into[/color][/color]
> play.[color=green][color=darkred]
> > > The
> > > > > most
> > > > > > you could be off within one calendar week is 6 days (if today is
> > > Sunday
> > > > > then
> > > > > > Saturday is 6 days away) so staying within the calendar week[/color][/color][/color]
WILL[color=blue][color=green]
> > stay[color=darkred]
> > > > > > within 6 days.
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Wayne Morgan
> > > > > > Microsoft Access MVP
> > > > > >
> > > > > >
> > > > > > "deko" <dje422@hotmail.com> wrote in message
> > > > > > news:2WNAb.66920$Lz3.5060@newssvr25.news.prodigy.c om...
> > > > > > > That seems to do it, except I think I need this:
> > > > > > >
> > > > > > > AND (dtmApptDate BETWEEN Date() -6 And Date() +6);
> > > > > > >
> > > > > > > For example:
> > > > > > >
> > > > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > > > FROM qry002
> > > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And
> > > DateAdd("d",
> > > > 6,
> > > > > > > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate]))) AND
> > > > (dtmApptDate
> > > > > > > BETWEEN Date() -6 And Date() +6);
> > > > > > >
> > > > > > > Or, is there a better way to narrow the range??
> > > > > > >
> > > > > > > Thanks again! I appreciate the help!!
> > > > > > >
> > > > > > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com>[/color][/color][/color]
wrote[color=blue][color=green]
> > in[color=darkred]
> > > > > > message
> > > > > > > news:MbNAb.35026$2z.28673@newssvr32.news.prodigy.c om...
> > > > > > > > Ok, you were close. Try
> > > > > > > >
> > > > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > > > DateAdd("d",-weekday([dtmApptDate])+1,[dtmApptDate]) And
> > > > DateAdd("d",
> > > > > 6,
> > > > > > > > DateAdd("d", -weekday([dtmApptDate])+1,[dtmApptDate])))
> > > > > > > >
> > > > > > > > >add 2 to -4 = 2 ??
> > > > > > > > No, add 2 to -4 is -2. Wednesday - 2 is Monday, not Sunday.
> > > > > > > >
> > > > > > > > --
> > > > > > > > Wayne Morgan
> > > > > > > > Microsoft Access MVP
> > > > > > > >
> > > > > > > >
> > > > > > > > "deko" <dje422@hotmail.com> wrote in message
> > > > > > > > news:dyDAb.66781$Je2.5936@newssvr25.news.prodigy.c om...
> > > > > > > > > Hi and thanks for the reply.
> > > > > > > > >
> > > > > > > > > Sunday is first day of the week, Saturday is last day.
> > > > > > > > >
> > > > > > > > > Here's what I'm trying to do:
> > > > > > > > >
> > > > > > > > > I've imported Outlook Appointments into an Access table --[/color]
> > each[color=darkred]
> > > > > record
> > > > > > > in
> > > > > > > > > the database has a number of these appointments associated[/color][/color]
> it.[color=green][color=darkred]
> > > > When
> > > > > > the
> > > > > > > > > user clicks a button to show all appointments for a[/color][/color]
> particular[color=green][color=darkred]
> > > > > record
> > > > > > > for
> > > > > > > > > the current week, I'm trying to query the table for all
> > > > appointments
> > > > > > > that
> > > > > > > > > fall within that range. So, if today is Wednesday, past
> > > > > appointments
> > > > > > > for
> > > > > > > > > Sunday, Monday and Tuesday would be returned as well as[/color][/color]
> future[color=green][color=darkred]
> > > > > > > > appointments
> > > > > > > > > for Thursday, Friday and Saturday (and of course any
> > > appointments
> > > > on
> > > > > > > > > Wednesday).
> > > > > > > > >
> > > > > > > > > I'm unsure of syntax after the AND statement and thought[/color][/color]
> that[color=green]
> > be[color=darkred]
> > > > my
> > > > > > > > error??
> > > > > > > > > perhaps I'm going about this all wrong??
> > > > > > > > >
> > > > > > > > > Thanks again for your help...
> > > > > > > > >
> > > > > > > > > assuming today is wednseday (4)...
> > > > > > > > >
> > > > > > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > > > > > FROM qry002
> > > > > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) 'add[/color][/color][/color]
2[color=blue][color=green][color=darkred]
> > > to -4
> > > > =
> > > > > 2
> > > > > > > ??
> > > > > > > > > AND DateAdd("d", 6,
> > > > > > > > DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
> > > > > > > > > 'add 6 to 2,4 ??
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com>[/color]
> > wrote[color=darkred]
> > > > in
> > > > > > > > message
> > > > > > > > > news:vYxAb.38994$se4.27540@newssvr31.news.prodigy. com...
> > > > > > > > > > What is your "first day of week"? Since you are adding 2[/color][/color]
> to[color=green][color=darkred]
> > > the
> > > > > > > WeekDay
> > > > > > > > > > result, I'm assuming Sunday is 1 and that you want the[/color][/color]
> work[color=green][color=darkred]
> > > week
> > > > > > which
> > > > > > > > > > starts on Monday. If today is Wednesday, that would be[/color][/color][/color]
day[color=blue]
> 4[color=green][color=darkred]
> > > (-4
> > > > +
> > > > > > 2)
> > > > > > > > > would
> > > > > > > > > > subtract 2 from Wednesday resulting in Monday. In the[/color][/color][/color]
next[color=blue][color=green][color=darkred]
> > > > > equation
> > > > > > > you
> > > > > > > > > then
> > > > > > > > > > add 6 to this. Monday is 2 plus 6 would be 8. If you are[/color]
> > after[color=darkred]
> > > > the
> > > > > > > > current
> > > > > > > > > > work week, then you probably want Friday (6) as your[/color][/color]
> second[color=green][color=darkred]
> > > > date,
> > > > > in
> > > > > > > > which
> > > > > > > > > > case the 6 below should be a 4.
> > > > > > > > > >
> > > > > > > > > > Sunday - 1
> > > > > > > > > > Monday - 2
> > > > > > > > > > Tuesday - 3
> > > > > > > > > > Wednesday - 4
> > > > > > > > > > Thursday - 5
> > > > > > > > > > Friday - 6
> > > > > > > > > > Saturday - 7
> > > > > > > > > >
> > > > > > > > > > This can be changed by specifying the first day of the[/color][/color][/color]
wee[color=blue]
> k[color=green]
> > in[color=darkred]
> > > > the
> > > > > > > > WeekDay
> > > > > > > > > > function.
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > Wayne Morgan
> > > > > > > > > > Microsoft Access MVP
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "deko" <dje422@hotmail.com> wrote in message
> > > > > > > > > > news:yBxAb.66685$kI2.12965@newssvr25.news.prodigy. com...
> > > > > > > > > > > This runs, but does not narrow to current week.
> > > > > > > > > > >
> > > > > > > > > > > suggestions appreciated!
> > > > > > > > > > >
> > > > > > > > > > > SELECT lngEid, dtmApptDate, Subject, Appt_ID
> > > > > > > > > > > FROM qry002
> > > > > > > > > > > WHERE (dtmApptDate BETWEEN
> > > > > > > > > > > DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate])[/color][/color][/color]
And[color=blue][color=green][color=darkred]
> > > > > > > DateAdd("d",
> > > > > > > > 6,
> > > > > > > > > > >[/color][/color][/color]
DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))[color=blue][color=green][color=darkred]
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>
>[/color]