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" <mike.macsween.nospam@btinternet.com> wrote in message
news:3fa824eb$0$52882$5a6aecb4@news.aaisp.net.uk.. .[color=blue]
> This looks like a bug to me.
>
> I have an expression on a report:
>
>[/color]
=Format(Sum(([Score]*[PercentOfGrade]*[PropDegree])*([ModuleCats]/[TotalCats[color=blue]
> ])),"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.[/color]
Some[color=blue]
> 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[/color]
is[color=blue]
> 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[/color]
format[color=blue]
> it changes it to "Fixed". I can enter either "0.00" or "Fixed" in Access[/color]
97.[color=blue]
> 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[/color]
of[color=blue]
> 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[/color]
currency[color=blue]
> 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[/color]
format[color=blue]
> 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[/color]
to[color=blue]
> be smart enough to figure it out if you actually have changed your[/color]
regional[color=blue]
> settings. So the help file is wrong. Again. Bah!
>
> OK, I've set these two front ends to use EXACTLY the same expression:
>
>[/color]
=Format(Sum(([Score]*[PercentOfGrade]*[PropDegree])*([ModuleCats]/[TotalCats[color=blue]
> ])),"Fixed")
>
> For a value whose true value is 5.425 Access 97 displays 5.42 and Access[/color]
XP[color=blue]
> displays 5.43 (it should be displaying 5.425 according to the help file,[/color]
as[color=blue]
> 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[/color]
help[color=blue]
> file says it should, and a difference in behaviour with rounding.
>
> Any explanations?
>
> Yours, Mike MacSween
>
>[/color]
