zero values

[Guest]

My query is meant to show only sales in 2006 but the report generates a
listing for a customer even if there are no '06 sales (ie there were sales
in 2004)... it reports a $0.00 Creating a very very long report

06YTDSales: Sum(Abs(Year([PostMonth])=(2006))*[CommVolume])

 

[Jeff Boyce]

Is there a question?

If you are looking to not include records where the amount is 0, you could
exclude the zero amount records (or the nulls) in your query, using the
Selection Criterion.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

Claudia:

Don't you just need to restrict the query on the year of the PostMonth
column, e.g.

SELECT Customer, SUM(CommVolume) AS 06YTDSales
FROM YourTable
WHERE YEAR(PostMonth) = 2006
GROUP BY Customer;

Ken Sheridan
Stafford, England

 

[Guest]

Maybe I don't understand......If I show <>0 as a criteria in the query...
when it totals in report format it leaves out any negative figures when
calculating, and the sum is incorrect

--
Claudia

Is there a question?

If you are looking to not include records where the amount is 0, you could
exclude the zero amount records (or the nulls) in your query, using the
Selection Criterion.

Regards

Jeff Boyce
<Office/Access MVP>

My query is meant to show only sales in 2006 but the report generates a
listing for a customer even if there are no '06 sales (ie there were
sales
in 2004)... it reports a $0.00 Creating a very very long report

06YTDSales: Sum(Abs(Year([PostMonth])=(2006))*[CommVolume])

 

[Jeff Boyce]

Could you post the SQL statement your query is using?

Regards

Jeff Boyce
<Office/Access MVP>

Maybe I don't understand......If I show <>0 as a criteria in the query...
when it totals in report format it leaves out any negative figures when
calculating, and the sum is incorrect

--
Claudia

Is there a question?

If you are looking to not include records where the amount is 0, you
could
exclude the zero amount records (or the nulls) in your query, using the
Selection Criterion.

Regards

Jeff Boyce
<Office/Access MVP>

My query is meant to show only sales in 2006 but the report generates
a
listing for a customer even if there are no '06 sales (ie there were
sales
in 2004)... it reports a $0.00 Creating a very very long report

06YTDSales: Sum(Abs(Year([PostMonth])=(2006))*[CommVolume])

 

[Guest]

SOLVED... Thank You.. I re-read your suggestion... I had been trying criteria
under the expression field... should be in the GROUP BY field... I use *2006*
to show just 2006, and for my TY LY comparision I used *2005* or *2006...

THANK YOU!!!! I've been struggling with this on and off for over a year
!!!!!!!!
--
Claudia

Maybe I don't understand......If I show <>0 as a criteria in the query...
when it totals in report format it leaves out any negative figures when
calculating, and the sum is incorrect

--
Claudia

Is there a question?

If you are looking to not include records where the amount is 0, you could
exclude the zero amount records (or the nulls) in your query, using the
Selection Criterion.

Regards

Jeff Boyce
<Office/Access MVP>

My query is meant to show only sales in 2006 but the report generates a
listing for a customer even if there are no '06 sales (ie there were
sales
in 2004)... it reports a $0.00 Creating a very very long report

06YTDSales: Sum(Abs(Year([PostMonth])=(2006))*[CommVolume])