StringDate >= Date -7

[Rod]

I have a StartDate of type String. I need to query for all StartDates within
the last week. Furthermore, I need to present those which are on Mondays
(later to separately present the rest of the days, but I figured I would have
to have different queries for each day - correct me if I am wrong). Here is
as far as I got:

SELECT [tblMy Outlook Calendar].StartDate, [tblMy Outlook Calendar].Subject,
CDate(Format([StartDate],"m/d/yyyy")) AS 1Wk, Weekday([StartDate]) AS Mon,
[tblMy Outlook Calendar].Categories
FROM [tblMy Outlook Calendar]
GROUP BY [tblMy Outlook Calendar].StartDate, [tblMy Outlook
Calendar].Subject, [tblMy Outlook Calendar].Categories, [tblMy Outlook
Calendar].Categories
HAVING (((CDate(Format([StartDate],"m/d/yyyy")))>=DateAdd("d",-7,Date()))
AND (([tblMy Outlook Calendar].Categories)="Appointment") AND
((Weekday([StartDate]))=2) AND (([tblMy Outlook
Calendar].Categories)="Appointment"));

Categories must be "Appointment".

 

[Duane Hookom]

You would want filter within the WHERE clause rather than the HAVING. Also,
since StartDate is string, you need to convert it to a date before comparing
it to other date values. I'm not sure why this is even a totals query.

SELECT StartDate, Subject, CDate([StartDate]) AS RealDate,
Weekday(CDate([StartDate])) AS StartDay, Categories
FROM [tblMy Outlook Calendar]
WHERE CDate([StartDate]) >=DateAdd("d",-7,Date())
AND Categories="Appointment"
AND WeekDay(CDate([StartDate]))=2
GROUP BY StartDate, Subject, CDate([StartDate]),
Weekday(CDate([StartDate])), Categories;

 

[Rod]

Thanks, much closer than my solution. The only thing I see is when I change
the query to show appointments w/in the last week that were on a Saturday I
received an appointment for next Saturday, 1/28/2006:

SELECT CDate([StartDate]) AS RealDate, [tblMy Outlook Calendar].Subject,
Weekday(CDate([StartDate])) AS StartDay
FROM [tblMy Outlook Calendar]
WHERE (((CDate([StartDate]))>=DateAdd("d",-7,Date())) AND (([tblMy Outlook
Calendar].Categories)="Appointment") AND ((Weekday(CDate([StartDate])))=7))
GROUP BY [tblMy Outlook Calendar].StartDate, CDate([StartDate]), [tblMy
Outlook Calendar].Subject, Weekday(CDate([StartDate])), [tblMy Outlook
Calendar].Categories;

You would want filter within the WHERE clause rather than the HAVING. Also,
since StartDate is string, you need to convert it to a date before comparing
it to other date values. I'm not sure why this is even a totals query.

SELECT StartDate, Subject, CDate([StartDate]) AS RealDate,
Weekday(CDate([StartDate])) AS StartDay, Categories
FROM [tblMy Outlook Calendar]
WHERE CDate([StartDate]) >=DateAdd("d",-7,Date())
AND Categories="Appointment"
AND WeekDay(CDate([StartDate]))=2
GROUP BY StartDate, Subject, CDate([StartDate]),
Weekday(CDate([StartDate])), Categories;

--
Duane Hookom
MS Access MVP
--

I have a StartDate of type String. I need to query for all StartDates
within
the last week. Furthermore, I need to present those which are on Mondays
(later to separately present the rest of the days, but I figured I would
have
to have different queries for each day - correct me if I am wrong). Here
is
as far as I got:

SELECT [tblMy Outlook Calendar].StartDate, [tblMy Outlook
Calendar].Subject,
CDate(Format([StartDate],"m/d/yyyy")) AS 1Wk, Weekday([StartDate]) AS Mon,
[tblMy Outlook Calendar].Categories
FROM [tblMy Outlook Calendar]
GROUP BY [tblMy Outlook Calendar].StartDate, [tblMy Outlook
Calendar].Subject, [tblMy Outlook Calendar].Categories, [tblMy Outlook
Calendar].Categories
HAVING (((CDate(Format([StartDate],"m/d/yyyy")))>=DateAdd("d",-7,Date()))
AND (([tblMy Outlook Calendar].Categories)="Appointment") AND
((Weekday([StartDate]))=2) AND (([tblMy Outlook
Calendar].Categories)="Appointment"));

Categories must be "Appointment".

 

[Rod]

Brain cramp! I added >=DateAdd("d",-7,Date()) And <=DateAdd("d",0,Date())
and the problem was solved.

Thanks MUCH!

Thanks, much closer than my solution. The only thing I see is when I change
the query to show appointments w/in the last week that were on a Saturday I
received an appointment for next Saturday, 1/28/2006:

SELECT CDate([StartDate]) AS RealDate, [tblMy Outlook Calendar].Subject,
Weekday(CDate([StartDate])) AS StartDay
FROM [tblMy Outlook Calendar]
WHERE (((CDate([StartDate]))>=DateAdd("d",-7,Date())) AND (([tblMy Outlook
Calendar].Categories)="Appointment") AND ((Weekday(CDate([StartDate])))=7))
GROUP BY [tblMy Outlook Calendar].StartDate, CDate([StartDate]), [tblMy
Outlook Calendar].Subject, Weekday(CDate([StartDate])), [tblMy Outlook
Calendar].Categories;

You would want filter within the WHERE clause rather than the HAVING. Also,
since StartDate is string, you need to convert it to a date before comparing
it to other date values. I'm not sure why this is even a totals query.

SELECT StartDate, Subject, CDate([StartDate]) AS RealDate,
Weekday(CDate([StartDate])) AS StartDay, Categories
FROM [tblMy Outlook Calendar]
WHERE CDate([StartDate]) >=DateAdd("d",-7,Date())
AND Categories="Appointment"
AND WeekDay(CDate([StartDate]))=2
GROUP BY StartDate, Subject, CDate([StartDate]),
Weekday(CDate([StartDate])), Categories;

--
Duane Hookom
MS Access MVP
--

I have a StartDate of type String. I need to query for all StartDates
within
the last week. Furthermore, I need to present those which are on Mondays
(later to separately present the rest of the days, but I figured I would
have
to have different queries for each day - correct me if I am wrong). Here
is
as far as I got:

SELECT [tblMy Outlook Calendar].StartDate, [tblMy Outlook
Calendar].Subject,
CDate(Format([StartDate],"m/d/yyyy")) AS 1Wk, Weekday([StartDate]) AS Mon,
[tblMy Outlook Calendar].Categories
FROM [tblMy Outlook Calendar]
GROUP BY [tblMy Outlook Calendar].StartDate, [tblMy Outlook
Calendar].Subject, [tblMy Outlook Calendar].Categories, [tblMy Outlook
Calendar].Categories
HAVING (((CDate(Format([StartDate],"m/d/yyyy")))>=DateAdd("d",-7,Date()))
AND (([tblMy Outlook Calendar].Categories)="Appointment") AND
((Weekday([StartDate]))=2) AND (([tblMy Outlook
Calendar].Categories)="Appointment"));

Categories must be "Appointment".