DateAdd Problems

N

NotGood@All

I'm trying to write some queries that count the number of events for the past
14 days, 10 weeks, 5 months, 3 qrts, and 2 years. I used this code to start
counting the days thinking all I have to do is get it to work then change the
'd' to 'ww' to 'q'. but I can't get the days to work. I posted this
question sometime this month but I got sick and I've been away and I can't
find my post to see if anyone helped me.

SELECT (DateCreated) AS Expr1, Count(*) AS [Output]
FROM qryForStatsPageOnly
WHERE ((((qryForStatsPageOnly.DateCreated)<=DateAdd("d",0,Date()))) and
(((qryForStatsPageOnly.DateCreated)>=DateAdd("d",-13,Date()))))
GROUP BY (DateCreated)
ORDER BY (DateCreated) DESC;

Thanks
NotGood@All
 
K

KARL DEWEY

You can not display all the dates (DateCreated) and count over a period
longer than a day at the same time.
Try this --
SELECT Sum(IIF([DateCreated] Between Date() and DateAdd("d",-13,Date()),
1,0) AS [Last two weeks], Sum(IIF([DateCreated] Between Date() and
DateAdd("ww",-10,Date()), 1,0) AS [Last ten weeks], Sum(IIF([DateCreated]
Between Date() and DateAdd("m",-5,Date()), 1,0) AS [Last five months],
Sum(IIF([DateCreated] Between Date() and DateAdd("q",-3,Date()), 1,0) AS
[Last three quarters], Sum(IIF([DateCreated] Between Date() and
DateAdd("yyyy",-2,Date()), 1,0) AS [Last two years]
FROM qryForStatsPageOnly;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A Weekly Count 3
Speed 1
Counting yearly totals 5
Display weeks from a Milestone 0
using tab control 3
DateAdd and Month functions 7
Allen Browne's Recurring events 0
Calculated Field in Query - Date Format 5

Top