By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 1,908 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

query to define current week?

P: n/a
This runs, but does not narrow to current week.

suggestions appreciated!

Expand|Select|Wrap|Line Numbers
  1. SELECT lngEid, dtmApptDate, Subject, Appt_ID
  2. FROM qry002
  3. WHERE (dtmApptDate BETWEEN
  4. DateAdd("d",-weekday([dtmApptDate])+2,[dtmApptDate]) And DateAdd("d", 6,
  5. DateAdd("d", -weekday([dtmApptDate])+2,[dtmApptDate])))
Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
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" <dj****@hotmail.com> wrote in message
news:yB*******************@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])))

Nov 12 '05 #2

P: n/a
"deko" <dj****@hotmail.com> wrote in message news:<yB*******************@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])))


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.
Nov 12 '05 #3

P: n/a
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" <co***************************@hotmail.com> wrote in message
news:vY*******************@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 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" <dj****@hotmail.com> wrote in message
news:yB*******************@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])))


Nov 12 '05 #4

P: n/a
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" <co***************************@hotmail.com> wrote in message
news:vY*******************@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 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" <dj****@hotmail.com> wrote in message
news:yB*******************@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])))


Nov 12 '05 #5

P: n/a
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" <dj****@hotmail.com> wrote in message
news:dy******************@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 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" <co***************************@hotmail.com> wrote in message news:vY*******************@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 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" <dj****@hotmail.com> wrote in message
news:yB*******************@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])))



Nov 12 '05 #6

P: n/a
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" <co***************************@hotmail.com> wrote in message
news:Mb******************@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" <dj****@hotmail.com> wrote in message
news:dy******************@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 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" <co***************************@hotmail.com> wrote in

message
news:vY*******************@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 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" <dj****@hotmail.com> wrote in message
news:yB*******************@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])))
>
>



Nov 12 '05 #7

P: n/a
> AND (dtmApptDate
BETWEEN Date() -6 And Date() +6);
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" <dj****@hotmail.com> wrote in message
news:2W******************@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" <co***************************@hotmail.com> wrote in message news:Mb******************@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" <dj****@hotmail.com> wrote in message
news:dy******************@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 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" <co***************************@hotmail.com> wrote in

message
news:vY*******************@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 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" <dj****@hotmail.com> wrote in message
> news:yB*******************@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])))
> >
> >
>
>



Nov 12 '05 #8

P: n/a
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" <co***************************@hotmail.com> wrote in message
news:Eh******************@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. 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" <dj****@hotmail.com> wrote in message
news:2W******************@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" <co***************************@hotmail.com> wrote in

message
news:Mb******************@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" <dj****@hotmail.com> wrote in message
news:dy******************@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 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" <co***************************@hotmail.com> wrote in
message
> news:vY*******************@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 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" <dj****@hotmail.com> wrote in message
> > news:yB*******************@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])))
> > >
> > >
> >
> >
>
>



Nov 12 '05 #9

P: n/a
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" <co***************************@hotmail.com> wrote in message
news:Eh******************@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. 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" <dj****@hotmail.com> wrote in message
news:2W******************@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" <co***************************@hotmail.com> wrote in

message
news:Mb******************@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" <dj****@hotmail.com> wrote in message
news:dy******************@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 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" <co***************************@hotmail.com> wrote in
message
> news:vY*******************@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 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" <dj****@hotmail.com> wrote in message
> > news:yB*******************@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])))
> > >
> > >
> >
> >
>
>



Nov 12 '05 #10

P: n/a
I think the problem was including [dtmApptDate] in the SELECT statement

developing...

"deko" <dj****@hotmail.com> wrote in message
news:0y*******************@newssvr25.news.prodigy. com...
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" <co***************************@hotmail.com> wrote in message news:Eh******************@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. 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" <dj****@hotmail.com> wrote in message
news:2W******************@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" <co***************************@hotmail.com> wrote in

message
news:Mb******************@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" <dj****@hotmail.com> wrote in message
> news:dy******************@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 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" <co***************************@hotmail.com> wrote
in > message
> > news:vY*******************@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 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" <dj****@hotmail.com> wrote in message
> > > news:yB*******************@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])))
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 12 '05 #11

P: n/a
nope... that was not the problem...

"deko" <dj****@hotmail.com> wrote in message
news:8E******************@newssvr25.news.prodigy.c om...
I think the problem was including [dtmApptDate] in the SELECT statement

developing...

"deko" <dj****@hotmail.com> wrote in message
news:0y*******************@newssvr25.news.prodigy. com...
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" <co***************************@hotmail.com> wrote in

message
news:Eh******************@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. 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" <dj****@hotmail.com> wrote in message
news:2W******************@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" <co***************************@hotmail.com> wrote in
message
> news:Mb******************@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" <dj****@hotmail.com> wrote in message
> > news:dy******************@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 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" <co***************************@hotmail.com> wrote
in > > message
> > > news:vY*******************@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 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" <dj****@hotmail.com> wrote in message
> > > > news:yB*******************@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])))
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 12 '05 #12

P: n/a
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" <dj****@hotmail.com> wrote in message
news:JG*****************@newssvr25.news.prodigy.co m...
nope... that was not the problem...

"deko" <dj****@hotmail.com> wrote in message
news:8E******************@newssvr25.news.prodigy.c om...
I think the problem was including [dtmApptDate] in the SELECT statement

developing...

"deko" <dj****@hotmail.com> wrote in message
news:0y*******************@newssvr25.news.prodigy. com...
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" <co***************************@hotmail.com> wrote in

message
news:Eh******************@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. 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" <dj****@hotmail.com> wrote in message
> news:2W******************@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" <co***************************@hotmail.com> wrote in > message
> > news:Mb******************@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" <dj****@hotmail.com> wrote in message
> > > news:dy******************@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 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" <co***************************@hotmail.com>

wrote in
> > > message
> > > > news:vY*******************@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 > 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" <dj****@hotmail.com> wrote in message
> > > > > news:yB*******************@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])))
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 12 '05 #13

P: n/a
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" <dj****@hotmail.com> wrote in message
news:Zq*******************@newssvr29.news.prodigy. com...
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" <dj****@hotmail.com> wrote in message
news:JG*****************@newssvr25.news.prodigy.co m...
nope... that was not the problem...

"deko" <dj****@hotmail.com> wrote in message
news:8E******************@newssvr25.news.prodigy.c om...
I think the problem was including [dtmApptDate] in the SELECT statement
developing...

"deko" <dj****@hotmail.com> wrote in message
news:0y*******************@newssvr25.news.prodigy. com...
> 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" <co***************************@hotmail.com> wrote in
message
> news:Eh******************@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.
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" <dj****@hotmail.com> wrote in message
> > news:2W******************@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" <co***************************@hotmail.com> wrote in > > message
> > > news:Mb******************@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" <dj****@hotmail.com> wrote in message
> > > > news:dy******************@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 > 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" <co***************************@hotmail.com> wrote in
> > > > message
> > > > > news:vY*******************@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
> > 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 wee

k in the
> > > > WeekDay
> > > > > > function.
> > > > > >
> > > > > > --
> > > > > > Wayne Morgan
> > > > > > Microsoft Access MVP
> > > > > >
> > > > > >
> > > > > > "deko" <dj****@hotmail.com> wrote in message
> > > > > > news:yB*******************@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])))
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 12 '05 #14

P: n/a
10-4 and thanks again for your help

"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:sJ*****************@newssvr33.news.prodigy.co m...
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" <dj****@hotmail.com> wrote in message
news:Zq*******************@newssvr29.news.prodigy. com...
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" <dj****@hotmail.com> wrote in message
news:JG*****************@newssvr25.news.prodigy.co m...
nope... that was not the problem...

"deko" <dj****@hotmail.com> wrote in message
news:8E******************@newssvr25.news.prodigy.c om...
> I think the problem was including [dtmApptDate] in the SELECT statement >
> developing...
>
> "deko" <dj****@hotmail.com> wrote in message
> news:0y*******************@newssvr25.news.prodigy. com...
> > 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" <co***************************@hotmail.com> wrote
in > message
> > news:Eh******************@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. 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" <dj****@hotmail.com> wrote in message
> > > news:2W******************@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" <co***************************@hotmail.com>
wrote
in
> > > message
> > > > news:Mb******************@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" <dj****@hotmail.com> wrote in message
> > > > > news:dy******************@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
> > 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" <co***************************@hotmail.com>

wrote
> in
> > > > > message
> > > > > > news:vY*******************@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
> > > 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 wee k
in
> the
> > > > > WeekDay
> > > > > > > function.
> > > > > > >
> > > > > > > --
> > > > > > > Wayne Morgan
> > > > > > > Microsoft Access MVP
> > > > > > >
> > > > > > >
> > > > > > > "deko" <dj****@hotmail.com> wrote in message
> > > > > > > news:yB*******************@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]))) > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>



Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.