Generic Inquiry Percentage Calculation not calculating correctly.

This seems to have cleaned up most of the data issues, except for the first example 52716/138000  should be 38% but result is 76% & the 2400/4800 is still showing 100%.   However,  in many other lines, this did fix the calculation issues.    What does the CDec part do?

As you can see from the data below,  with the changes recommended by Daryl, most of the calculations are correct.  However, Line number 1 is incorrect (highlighted).   The calculation is Project Actual Amount / Project Budget amount.  This should be 38%   I have no clue how it is getting to 76% in this example.

This is formula in the results grid.   Revised Amount = Project Actual Amount (the caption was changed to make more sense to the users)

=CInt(IIF([BudgetRevenue.CuryRevisedAmount]=0, 0, CDec([BudgetRevenue.CuryActualAmount])/[BudgetRevenue.CuryRevisedAmount]*100))

I can’t explain why one row would be behaving differently. I did notice that 38.2 (which is the correct result) is half of 76.4 (which I’m assuming is the rounded result you’re seeing.

CDec converts the amount to a decimal. The reason this works is because in code, dividing an integer by an integer will always result in an integer. Converting one to a decimal will result in the answer being a decimal, therefore not rounded prematurely.

Thanks for the help!  I appreciate it.

I encountered a very odd scenario today where CDec() was rounding a converted string to the nearest integer. CDbl() was working fine and is enough precision for our needs. In my case, I was curious if there was an issue with commas or decimal points.

The only way I can manufacture your results is by performing the operation twice with comma splits, but I think it’s worth noting that the row below, which also has an extra digit compared to other rows, is a factor of two too small.
My only takeaway has been to use CDbl() instead of CDec(), but I haven’t been able to find anyone else with the same issue.
I hope no one else stumbles across bugs like this, but just in case!

-Aaron

This seems like a hack that hasn’t be resolved for years.

Someone on another thread suggested having global schema fields that can be used to format anything without having to add a table to the query that isn’t used.

I love that idea

Hi ​@scottspencer82,

I do not think you need CDec for CuryActualAmount as it is already a decimal field. I would recommend the parenthesis around the division and then multiply it with 100 for percentage. I have modified the formula as below. Hope that helps.

=CInt(IIF([BudgetRevenue.CuryRevisedAmount]=0, 0, ([BudgetRevenue.CuryActualAmount]/[BudgetRevenue.CuryRevisedAmount])*100))