Percent Formula Help

[Bennie47250]

Using Access 2003

I give up; I have attempted to get this to work for way to long.

Need to determine percent of increase or decrease from the previous quarter.

Using two fields CurrentQtr and PrevQtr. The CurrentQtr value is 400, the PrevQrt value is 293. The percent of increase is 27.

The formula I’m using is (PrevQtr-CurrentQtr) / PrevQtr. Access is returning 0 rather than 27. What am I doing wrong?

The field data type is number and I have changed the Field Size and Format to several different settings with no success.

Thanks
Bennie

 

[r937]

try 100.0 * (PrevQtr-CurrentQtr) / PrevQtr

r937.com | rudy.ca

 

[Bennie47250]

Let me add that I'm attempting this in an Update query. The formula is updating a field named PercentOfChange.

The formula works in a Select query, it blows up when it is updating the PercentOfChange field.

 

[Bennie47250]

r937, that did the trick. Thanks.

What is this doing?

 

[Golom]

It is likely that your fields were integer data types so the division that you were doing was being done using integer arithemetic. Since integers can't handle fractions the 0.27 was truncated to zero. Rudy's code just forces floating point calculation (note 100[red].0[/red]) and produces a whole number rather than a fraction.