Rounding, format(), fixed, differences between Access 97 and XP.

[Mike MacSween]

This looks like a bug to me.

I have an expression on a report:

=Format(Sum(([Score]*[PercentOfGrade]*[PropDegree])*([ModuleCats]/[TotalCats
])),"0.00")

Score is byte
PercentOfGrade is double
PropDegree is single
ModuleCats is byte
TotalCats is integer

This is how the expression is in Access 97. Today I converted it to XP. Some
of the figures differed by 0.01 between the two versions. These are two
different front ends linked to the same back end (which is Access 97
format).

An example is a series of calculations which gives a result of 5.4250000 is
rounded up in XP to 5.43 and down in 97 to 5.42

I investigate. In the XP version the "0.00" has been changed to "Fixed". I
change it back. It won't have it. Every time I try to use "0.00" as a format
it changes it to "Fixed". I can enter either "0.00" or "Fixed" in Access 97.
Though it DOESN'T change the result.

I look at the help file which for 97 reports:

'Fixed Display at least one digit to the left and two digits to the right of
the decimal separator.'

but for XP reports (eventually!):

'Display at least one digit; follow the settings specified in Regional
Settings in Windows Control Panel for negative amounts, decimal and currency
symbols, and decimal places'

I'll experiment. I change my regional settings to 3 decimal places and
reboot. Same thing. Access XP doesn't now try to correct "0.000" as a format
to "Fixed" which is sort of what you'd expect. What I mean is it appears
initially that if you enter "0.00" as a numeric format then Access XP
recognizes that as your regional numeric format, and changes it to 'Fixed'
because it thinks that's what you really mean. Hmmph. But it doesn't seem to
be smart enough to figure it out if you actually have changed your regional
settings. So the help file is wrong. Again. Bah!

OK, I've set these two front ends to use EXACTLY the same expression:

=Format(Sum(([Score]*[PercentOfGrade]*[PropDegree])*([ModuleCats]/[TotalCats
])),"Fixed")

For a value whose true value is 5.425 Access 97 displays 5.42 and Access XP
displays 5.43 (it should be displaying 5.425 according to the help file, as
I've changed my regional settings to 3 decimal places).

There seem to be 2 things. A bug where Access XP doesn't work like the help
file says it should, and a difference in behaviour with rounding.

Any explanations?

Yours, Mike MacSween

[Allen Browne]

Mike, this isn't really surprising.

Some of the factors involved:

1. Floating point numbers can never be stored to precise values. They are
always subject to variations.

2. A97 and A2002 use completely different versions of Visual Basic. Some
differences are to be expected.

3. Your calculation is using a wild mix of data types without explicitly
typecasting.

4. There is a Round() function in A2002 which may be better than converting
the result to a string subtype with Format().

5. Access 2002 (at least) is supposed to round to the nearest even digit. It
always does this exactly for floating point numbers (as #1 above), but it
does if you convert the result to Currency.

Typecasting to Currency - i.e. CCur() - before rounding - Round() - might
give the best result. If you need a Round() for Access 97, there is one
here:
http://www.mvps.org/access/modules/mdl0054.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .

This looks like a bug to me.

I have an expression on a report:

=Format(Sum(([Score]*[PercentOfGrade]*[PropDegree])*([ModuleCats]/[TotalCats ])),"0.00")

Score is byte
PercentOfGrade is double
PropDegree is single
ModuleCats is byte
TotalCats is integer

This is how the expression is in Access 97. Today I converted it to XP. Some of the figures differed by 0.01 between the two versions. These are two
different front ends linked to the same back end (which is Access 97
format).

An example is a series of calculations which gives a result of 5.4250000 is rounded up in XP to 5.43 and down in 97 to 5.42

I investigate. In the XP version the "0.00" has been changed to "Fixed". I
change it back. It won't have it. Every time I try to use "0.00" as a format it changes it to "Fixed". I can enter either "0.00" or "Fixed" in Access 97. Though it DOESN'T change the result.

I look at the help file which for 97 reports:

'Fixed Display at least one digit to the left and two digits to the right of the decimal separator.'

but for XP reports (eventually!):

'Display at least one digit; follow the settings specified in Regional
Settings in Windows Control Panel for negative amounts, decimal and currency symbols, and decimal places'

I'll experiment. I change my regional settings to 3 decimal places and
reboot. Same thing. Access XP doesn't now try to correct "0.000" as a format to "Fixed" which is sort of what you'd expect. What I mean is it appears
initially that if you enter "0.00" as a numeric format then Access XP
recognizes that as your regional numeric format, and changes it to 'Fixed'
because it thinks that's what you really mean. Hmmph. But it doesn't seem to be smart enough to figure it out if you actually have changed your regional settings. So the help file is wrong. Again. Bah!

OK, I've set these two front ends to use EXACTLY the same expression:

=Format(Sum(([Score]*[PercentOfGrade]*[PropDegree])*([ModuleCats]/[TotalCats ])),"Fixed")

For a value whose true value is 5.425 Access 97 displays 5.42 and Access XP displays 5.43 (it should be displaying 5.425 according to the help file, as I've changed my regional settings to 3 decimal places).

There seem to be 2 things. A bug where Access XP doesn't work like the help file says it should, and a difference in behaviour with rounding.

Any explanations?

Yours, Mike MacSween