A Weekly Count

[NotGood@All]

I'm trying to get a count of things that happended for the past 6 weeks. I'm
trying to modify this code:

SELECT (DateCreated) AS Expr1, Count(*) AS [Output]
FROM qryForStatsPageOnly
WHERE (qryForStatsPageOnly.DateCreated)<=DateAdd("ww",6,Date())
GROUP BY (DateCreated)
ORDER BY (DateCreated) DESC;

but no matter what I put in place of the "6" it works just like I have "d"
in the "ww". When I read help on DateAdd, it reads like all you have to do
is replace a "d" with "m" with "yyyy".....

 

[John W. Vinson]

I'm trying to get a count of things that happended for the past 6 weeks. I'm
trying to modify this code:

SELECT (DateCreated) AS Expr1, Count(*) AS [Output]
FROM qryForStatsPageOnly
WHERE (qryForStatsPageOnly.DateCreated)<=DateAdd("ww",6,Date())
GROUP BY (DateCreated)
ORDER BY (DateCreated) DESC;

but no matter what I put in place of the "6" it works just like I have "d"
in the "ww". When I read help on DateAdd, it reads like all you have to do
is replace a "d" with "m" with "yyyy".....

Your query as written is doing two things wrong: first, it's asking for all
records where DateCreated is earlier than six weeks IN THE FUTURE (you're
adding +6 weeks to Date() and finding all DateCreated less than that value).
Another possible problem if I'm interpreting your subject correctly is that
you're grouping by DateCreated so rather than a count per week you're getting
a count per day (or even per record if DateCreated contains a time portion).

Try

SELECT DateCreated, Count(*) AS Output
FROM qryForStatsPageOnly
WHERE DateCreated >= DateAdd("ww", -6, Date())

to get a count per day; post back if you want a count by week, indicating how
you want the result to appear.

 

[Douglas J. Steele]

If you want what's been modified in the past six weeks, I'd think you'd want

WHERE (qryForStatsPageOnly.DateCreated)>=DateAdd("ww",-6,Date())

Alternatively, you could use

WHERE (qryForStatsPageOnly.DateCreated)>=DateAdd("d",-42,Date())

 

[Marshall Barton]

I'm trying to get a count of things that happended for the past 6 weeks. I'm
trying to modify this code:

SELECT (DateCreated) AS Expr1, Count(*) AS [Output]
FROM qryForStatsPageOnly
WHERE (qryForStatsPageOnly.DateCreated)<=DateAdd("ww",6,Date())
GROUP BY (DateCreated)
ORDER BY (DateCreated) DESC;

but no matter what I put in place of the "6" it works just like I have "d"
in the "ww". When I read help on DateAdd, it reads like all you have to do
is replace a "d" with "m" with "yyyy".....

If you want the past 6 weeks, maybe it should be more like:

SELECT DateDiff("d", [DateCreated], Date()) \ 7 As Expr1,
Count(*) AS [Output]
FROM qryForStatsPageOnly
WHERE DateDiff("d", [DateCreated], Date()) \ 7 < 6
GROUP BY DateDiff("d", [DateCreated], Date()) \ 7