Counting yearly totals

[NotGood@All]

I'm trying to count the number of lines in a query that happend by year. I
have 56000+ items for this year but this line of code only gives me 7000.
Can someone tell me what is wrong with the code.

DCount("DateCreated","qryForStatsPageOnly","DateDiff('y', DateCreated,
Now)=0")

Thank You
NotGood@All

 

[Graham Mandeno]

Hi NG@A

For some reason known only to someone who has probably long since stopped
working for Microsoft, 'y' in a DateDiff means "day of year", and functions
identically to 'd'. To calculate a difference of years, you must use
'yyyy'.

However, I suspect using the Year function would be much more efficient:

DCount("*","qryForStatsPageOnly","Year(DateCreated)=" & Year(Now))

 

[Marshall Barton]

I'm trying to count the number of lines in a query that happend by year. I
have 56000+ items for this year but this line of code only gives me 7000.
Can someone tell me what is wrong with the code.

DCount("DateCreated","qryForStatsPageOnly","DateDiff('y', DateCreated,
Now)=0")

The 'y' datepart is the day of the year. You want to use
the 'yyyy' code.

I think its easier to use:

DCount("DateCreated","qryForStatsPageOnly",
"Year(DateCreated=Year(Date())")

 

[NotGood@All]

Graham, Thank you. I'm using DCount to total 40 different date counts. Is
DCount the best way or is there a better way?
--
NotGood@All

Hi NG@A

For some reason known only to someone who has probably long since stopped
working for Microsoft, 'y' in a DateDiff means "day of year", and functions
identically to 'd'. To calculate a difference of years, you must use
'yyyy'.

However, I suspect using the Year function would be much more efficient:

DCount("*","qryForStatsPageOnly","Year(DateCreated)=" & Year(Now))


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm trying to count the number of lines in a query that happend by year.
I
have 56000+ items for this year but this line of code only gives me 7000.
Can someone tell me what is wrong with the code.

DCount("DateCreated","qryForStatsPageOnly","DateDiff('y', DateCreated,
Now)=0")

Thank You
NotGood@All

 

[Graham Mandeno]

When you say "total 40 different date counts", do you mean that you have 40
different date fields in your query, and you want to count how many values
in each field fall in the current year?

If so, I suggest you write a single query as follows:

SELECT
Abs(Sum(Year(DateCreated)=Year(Now))) as CountDateCreated,
Abs(Sum(Year(Date2)=Year(Now))) as CountDate2,
Abs(Sum(Year(Date3)=Year(Now))) as CountDate3,
... repeat for other fields
FROM qryForStatsPageOnly;

The expression Year(DateCreated)=Year(Now) will return True if DateCreated
falls in the current year, otherwise False. True has a value of -1 so a Sum
of all the True values will give a negative number of the count. False
always has a value of zero, so will not affect the count. Finally, the Abs
function converts the negative count to a positive count.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, Thank you. I'm using DCount to total 40 different date counts.
Is
DCount the best way or is there a better way?
--
NotGood@All

Hi NG@A

For some reason known only to someone who has probably long since stopped
working for Microsoft, 'y' in a DateDiff means "day of year", and
functions
identically to 'd'. To calculate a difference of years, you must use
'yyyy'.

However, I suspect using the Year function would be much more efficient:

DCount("*","qryForStatsPageOnly","Year(DateCreated)=" & Year(Now))


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm trying to count the number of lines in a query that happend by
year.
I
have 56000+ items for this year but this line of code only gives me
7000.
Can someone tell me what is wrong with the code.

DCount("DateCreated","qryForStatsPageOnly","DateDiff('y', DateCreated,
Now)=0")

Thank You
NotGood@All

 

[open a adobe file from a command button]

I have a query that only has 1 field, the "DateCreated", I'm counting the
last 15 days, the last 10 weeks, the last 12 months, the last 4 quarters, and
the last 3 years, all by using Dcount, just changing the d, w, m, q, and
yyyy. It worked so I used it. Now it is slow but that's all I know.

When you say "total 40 different date counts", do you mean that you have 40
different date fields in your query, and you want to count how many values
in each field fall in the current year?

If so, I suggest you write a single query as follows:

SELECT
Abs(Sum(Year(DateCreated)=Year(Now))) as CountDateCreated,
Abs(Sum(Year(Date2)=Year(Now))) as CountDate2,
Abs(Sum(Year(Date3)=Year(Now))) as CountDate3,
... repeat for other fields
FROM qryForStatsPageOnly;

The expression Year(DateCreated)=Year(Now) will return True if DateCreated
falls in the current year, otherwise False. True has a value of -1 so a Sum
of all the True values will give a negative number of the count. False
always has a value of zero, so will not affect the count. Finally, the Abs
function converts the negative count to a positive count.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, Thank you. I'm using DCount to total 40 different date counts.
Is
DCount the best way or is there a better way?
--
NotGood@All

Hi NG@A

For some reason known only to someone who has probably long since stopped
working for Microsoft, 'y' in a DateDiff means "day of year", and
functions
identically to 'd'. To calculate a difference of years, you must use
'yyyy'.

However, I suspect using the Year function would be much more efficient:

DCount("*","qryForStatsPageOnly","Year(DateCreated)=" & Year(Now))


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm trying to count the number of lines in a query that happend by
year.
I
have 56000+ items for this year but this line of code only gives me
7000.
Can someone tell me what is wrong with the code.

DCount("DateCreated","qryForStatsPageOnly","DateDiff('y', DateCreated,
Now)=0")

Thank You
NotGood@All