Field going to zero

[TooManyReports]

I have a calculated field from another query that I am pulling in from. When I view the field on the original query, it looks correct, but on the secondary query the value goes to 0 or 100 or 50 percent. When I do the calculation in query, it still does the same thing. Calcuation is (Current Year value / Last Year Value) - 1 = (682/752)-1 should be 9.308%

 

[TexanInParis]

What is the SQL of the two queries? It sounds like you have a format defined in the second one or have used an expression that converts the result to Integer.

 

[TooManyReports]

Well thought I had this solved, but the solution that I used will not work for my problem I have again.

SELECT [Net/Net Sum].WEEK_ID, [Net/Net Sum].[Sale Area], [Net/Net Sum].Category, [Net/Net Sum]![SumOfCurrent Units]/[Net/Net Sum]![SumOfPrevious Units]-1 AS [Comp Unit Totals], [Net/Net Sum]![SumOfCurrent Sales $]/[Net/Net Sum]![SumOfPrevious Sales]-1 AS [Comp Sales Totals], ([Net/Net Sum]![SumOfCurrent Sales $]-[Net/Net Sum]![SumOfCurrent Cost])/([Net/Net Sum]![SumOfPrevious Sales]-[Net/Net Sum]![SumOfPrevious Cost])-1 AS [Comp Profit % Total], 1-([Net/Net Sum]![SumOfPrevious Cost]/[Net/Net Sum]![SumOfPrevious Sales]) AS [LY Sales GM % Total], 1-([Net/Net Sum]![SumOfCurrent Cost]/[Net/Net Sum]![SumOfCurrent Sales $]) AS [Total CY GM %]
FROM [Net/Net Sum];

This is based off another query that is summing information from a table. I can see the Totals in datasheet view, but when I look at the pivot table or pivot chart, it goes to zero.

 

[TexanInParis]

Are any of the calculated fields showing with decimals? You could try using 1.0 instead of 1 to help preserve the floating point, but if any of the values in each expression is floating point, then the query engine should be using that. The only other thing that might help is to slap CDbl around each of the expressions.

Well thought I had this solved, but the solution that I used will not work for my problem I have again.

SELECT [Net/Net Sum].WEEK_ID, [Net/Net Sum].[Sale Area], [Net/Net Sum].Category, 
[Net/Net Sum]![SumOfCurrent Units]/[Net/Net Sum]![SumOfPrevious Units]-1 AS [Comp Unit Totals], 
[Net/Net Sum]![SumOfCurrent Sales $]/[Net/Net Sum]![SumOfPrevious Sales]-1 AS [Comp Sales Totals], 
([Net/Net Sum]![SumOfCurrent Sales $]-[Net/Net Sum]![SumOfCurrent Cost])/([Net/Net Sum]![SumOfPrevious Sales]-[Net/Net Sum]![SumOfPrevious Cost])-1 AS [Comp Profit % Total], 
1-([Net/Net Sum]![SumOfPrevious Cost]/[Net/Net Sum]![SumOfPrevious Sales]) AS [LY Sales GM % Total], 
1-([Net/Net Sum]![SumOfCurrent Cost]/[Net/Net Sum]![SumOfCurrent Sales $]) AS [Total CY GM %]
FROM [Net/Net Sum];

This is based off another query that is summing information from a table. I can see the Totals in datasheet view, but when I look at the pivot table or pivot chart, it goes to zero.

 

[TooManyReports]

Thanks a lot. The CDbl was exactly what was missing.

 

[TexanInParis]

Great! I have no clue why it was insisting on displaying integers.