473,846 Members | 1,975 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
Nov 12 '05 #1
1 15486
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************ ******@btintern et.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

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
11741
by: Raoul Watson | last post by:
By any chance, anyone got a rounding routine that does a work around the VB "round" bug? I still find it amazing that a company as large as Microsoft would put out a math package that is so problematic.
3
9427
by: Dalan | last post by:
Is there any code available to address currency rounding problems in Access 97? Apparently, selecting currency type table fields does not resolve the problem. For instance, in my form I have a price of item field (say $49.95), and a percentage discount field (say 10% = $5.00), and calculated net cost field of the two. Access seemingly doesn't understand banker's rules as the resulting total is $44.96. Could this be a bug? Why does it...
6
3427
by: DaveK | last post by:
I am upgrading an MS Access 97 application which uses the format function to round decimal numbers to two places e.g. format(dblValue, "standard") Having done an initial study of this function I can see no obvious pattern in how it does rounding, it is certainly not bankers rounding. I need to replicate the rounding algorithm used in the new application so that the data will be consistent, so if anyone knows the rounding
4
22048
by: sql guy123 | last post by:
HI, I'm pretty new to MS SQL, My problem is setting .... =Format$(Date(),"mm" & "/1/" & "YYYY") (Which is from my MS ACCESS database)
15
15060
by: Br | last post by:
Cint(3/2) = 2 (rounded up, I would have thought the answer would be 1) Cint(5/2) = 2 (rounded down) The Access help says Cint() rounds to the nearest even number, but why?? Do I need to "roll my own" integer conversion to get it to always round up or down? I looked at Cint(3\2) and Cint(5\2) which seems to consistently round
5
2261
by: Jason | last post by:
I am having a rounding problem in a value i am trying to display in VB.NET. If I have the following code: Dim x As Single = 2726.795 Dim y As Single = Math.Round(2726.795, 2) Dim s as String = String.Format("{0:0.00}", y) The value of s is 2726.79 If I have the following code: Dim y As Single = Math.Round(2726.795, 2)
6
2254
by: shaqattack1992-newsgroups | last post by:
Hello Everyone, I'm using the following on a form in my database: =(-Int(-Sum((IIf(=Yes,((*)+()), (((*)+)*1.06))*100))))/100 In this case, I want to calculate a total for an order. If the LineTaxExempt field is checked (meaning tax exempt), the total is
3
2285
by: gabo22003 | last post by:
Hello, I've inherited a tax report where all of the financial numbers are currency.This is a detailed report that lists amount, tax and total for that day ( total is listed in the database). The report gets its information from a query. At this point all is working Then in the report footer I am trying to total the entire amount for that particular day. The basic formula in use is: =Sum()
206
13384
by: md | last post by:
Hi Does any body know, how to round a double value with a specific number of digits after the decimal points? A function like this: RoundMyDouble (double &value, short numberOfPrecisions) It then updates the value with numberOfPrecisions after the decimal
0
9879
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10976
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10640
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10705
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9477
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5714
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5906
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4521
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3157
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.