MS Access Report Formula Error

Question

I have built a database that allows users to select their name from a list on a form and then a report is generated. I am having trouble with the "Year over Year Change" field on the sub-report to show what actually happened. Sometimes it shows up/down correctly, but other times the calculation is incorrect so it shows "Up" when we are down and "Down" when we are up. The formula I have is: 

=IIf([Reports]![ManagerDashboardR]![WrittenPremium2019R]![LineEstimatedPremium]<[Reports]![ManagerDashboardR]![WrittenPremium2018R]![LineEstimatedPremium],"Down","Up")

I have included a picture of what I am talking about so you can see. If anyone knows how to correct this problem, I would appreciate the assistance.

Thank you.

Answer 1

Rather than trying to compare values in the report I would suggest computing the values in a query which can be used as the subreport's RecordSource property.  Without knowing the details of the base tables from which the data is drawn I can't give you the query of course, but here's an example using Northwind (with the order dates adjusted to cover 2017/2018):

SELECT FORMAT([Order Date] ,"mmmm yyyy") AS OrderMonth,

SUM([Unit Price]*Quantity) AS Total,

     IIF(NZ((SELECT SUM([Unit Price]*Quantity)

                 FROM Orders AS O2 INNER JOIN [Order Details] As OD2

                 ON O2.[Order ID] = OD2.[Order ID]

                 WHERE YEAR(O2.[Order Date]) =  2017

                 AND MONTH(O2.[Order Date]) =  MONTH(O1.[Order Date])),0)

    >SUM([Unit Price]*Quantity),"Down","Up") AS Movement

FROM Orders AS O1 INNER JOIN [Order Details] As OD1

ON O1.[Order ID] = OD1.[Order ID]

WHERE YEAR([Order Date]) = 2018

GROUP BY YEAR([Order Date]), MONTH([Order Date]),

FORMAT([Order Date] ,"mmmm yyyy");

It should not be too difficult to compute the movement of the yearly totals directly in the report, or you can use an amended version of the above, grouping by year only:

SELECT YEAR([Order Date]) AS OrderYear,

SUM([Unit Price]*Quantity) AS Total,

     IIF(NZ((SELECT SUM([Unit Price]*Quantity)

                 FROM Orders AS O2 INNER JOIN [Order Details] As OD2

                 ON O2.[Order ID] = OD2.[Order ID]

                 WHERE YEAR(O2.[Order Date]) =  2017),0)

    >SUM([Unit Price]*Quantity),"Down","Up") AS Movement

FROM Orders AS O1 INNER JOIN [Order Details] As OD1

ON O1.[Order ID] = OD1.[Order ID]

WHERE YEAR([Order Date]) = 2018

GROUP BY YEAR([Order Date]);

Neither of the above allows for exact ties of course, which would be returned as 'Up', but that's a remote possibility.

Answer 2

It's hard to know without seeing your project, but to begin with:

Are you sure the IIF statement is correct? In other words, are you sure that you're comparing the correct line in the 2018 subreport? It seems you're comparing values in the LineEstimatedPremium, but you don't refer to a specific record in that dataset.

Can you not include the previous year's totals in your query? If you can, you can use that instead of looking at other objects on the main report.