Total Sales by Year

[Cameron]

In my report I am totaling sales by each year. I do this by using a text box
for each year with this: =DCount("SalesDate","Sales","SalesDate>=#1/1/2009#
And SalesDate<#1/1/2010#") This gives me a sum of sales for 2009. I have a
text box for each of the last 5 years.

Is there a better way to do this?
Is there a way I can use a function like DatePart to use just the year 2009
instead of having to use the >= and < operators?

 

[KARL DEWEY]

This work for me --
DCount("SalesDate","Sales","Year(SalesDate)=2009")

 

[John Spencer]

You can use the following, although it will be slower than what you are
currently using.

=DCount("SalesDate","Sales","Year(SalesDate)=2009")

You would probably be better off in terms of performance if you built a report
based on a query to give you just the counts for the last five years and then
included that as a sub-report.

Another headache is that with your current report, you will need to update the
control sources each year. You could at least change your five controls. For
instance to get data for the current year and the previous 4 years you would
have controls with sources like:

=DCount("SalesDate","Sales","Year(SalesDate)=" & Year(Date())-0)
=DCount("SalesDate","Sales","Year(SalesDate)=" & Year(Date())-1)
=DCount("SalesDate","Sales","Year(SalesDate)=" & Year(Date())-2)
=DCount("SalesDate","Sales","Year(SalesDate)=" & Year(Date())-3)
=DCount("SalesDate","Sales","Year(SalesDate)=" & Year(Date())-4)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County