473,287 Members | 1,899 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

query to define current week?

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
14 5068
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
"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
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
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
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
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
> 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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Alicia | last post by:
Yes, but will that skip a week and group by the date for me? I basically wanted something that would do a count of the dates, then group them by their week name.. BEFORE: Resource Date ...
6
by: Bill R via AccessMonster.com | last post by:
I have a query: SELECT tblCalendar.CalendarDay AS LastSunday FROM tblCalendar WHERE (((tblCalendar.CalendarDay)>=(Now()-7) And (tblCalendar.CalendarDay)...
5
by: Bob Alston | last post by:
I have a where used utility that shows where any table or query is used - in a query, form or table. I would like some kind of diagram that shows these relations. I have some reports that...
14
by: Tina | last post by:
My employer tracks productivity/performance of clinicians (how much they bill) each week, its averages for the month, and the 6 months. These averages are compared to their expected productivity....
1
by: Rob Woodworth | last post by:
Hi, I'm having serious problems getting my report to work. I need to generate a timesheet report which will contain info for one employee between certain dates (one week's worth of dates). I...
5
by: sara | last post by:
I have reports that run from a form where the user can choose a date range, or they run automatically for a week in the "Weekly Reports" option. I created 2 queries and 2 reports - one query...
5
by: bruce24444 | last post by:
I have a database which assigns warranty claims to people with a main screen showing number of files assigned to each person. The number assigned shows day, week, month and year numbers so they can...
1
osward
by: osward | last post by:
Hi everyone, Background 1. I have a table that consits 400+ rows of data and is growing by day. The table already has paging links at the bottom but I restricted to display rows of data only >=...
4
by: zion4ever | last post by:
Hello good people, Please bear with me as this is my first post and I am relative new to ASP. I do have VB6 experience. I have a form which enables users within our company to do an intranet...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.