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])))
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])))
"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.
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])))
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])))
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])))
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]))) > >
> 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]))) > > > > > >
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]))) > > > > > > > > > > > >
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]))) > > > > > > > > > > > >
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]))) > > > > > > > > > > > > > > > > > > > >
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]))) > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
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]))) > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
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]))) > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
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]))) > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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 ...
|
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)...
|
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...
|
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....
|
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...
|
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...
|
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...
|
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 >=...
|
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...
|
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"....
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
| |