rounding making result of calculations look incorrect

  • Thread starter Thread starter Helen
  • Start date Start date
H

Helen

Hi all, I've got a bound form on which some text boxes are populated by a
query. Other textboxes contain calculations. The results from the
calculations are being compared to the same calcs carried out in Excel. My
problem in Access is with the rounding of the data, as follows:

Actual in Access data looks something like this:
7.4443257 +
8.344189
15.7885147 is the result

On the form, with formatting applied to show 2 decimal places it looks like
this

7.44 +
8.34
15.79 as the result

Clearly 7.44 + 8.34 does not equal 15.79, so clients receiving letters from
the database (it's a financial application) may be somewhat unsettled to
think that the people looking after their money don't know how to add up!
And of course the Excel spreadsheet that the Access application is being
tested against always gets the "right" result, as the data is being keyed in
correct to only 2 decimal places.

Any suggestions as to how I can make Access display 15.78 as the result?
Thanks in advance for any help - it will be greatly appreciated.

Best regards,
Helen
 
Round the input values before doing the addition and you'll get the value
you're after.
 
Hi Wayne, thanks very much for your reply, but this is where I need the
help. I did look at trying to round the values in the query (ie. before I
display them on the form where the addition is taking place) but don't see
any Round function in Access 97. I'm using Access 97 with Win NT 4. Any
suggestions would be gratefully appreciated?
Thanks, Helen
 
Create your own round function in a module. Example for 2 decimals:

Public Function MyRound(curNumber As Currency) As Currency
Dim curRounded As Currency
curRounded = Int(curNumber * 100 + 0.5) / 100
MyRound = curRounded
End Function

Then, in your query, you would call the function in a calculated field.

RoundField1: MyRound([Table1].[Field1])

Once you do this, this field will not be updateable.

Name the function MyRound or something other than Round so that you won't
have a conflict with the built-in Round function should you upgrade to
Access 2000 or newer. The built in Round function uses "banker's" or
"scientific" rounding. It rounds .5 to the nearest even number instead of
always rounding up.
 
Fantastic, thanks so much for this Wayne. I wasn't anticipating having to
take this approach, thinking there must be something I was overlooking in
Access... I'll have a go over the weekend.
Best regards, Helen




Wayne Morgan said:
Create your own round function in a module. Example for 2 decimals:

Public Function MyRound(curNumber As Currency) As Currency
Dim curRounded As Currency
curRounded = Int(curNumber * 100 + 0.5) / 100
MyRound = curRounded
End Function

Then, in your query, you would call the function in a calculated field.

RoundField1: MyRound([Table1].[Field1])

Once you do this, this field will not be updateable.

Name the function MyRound or something other than Round so that you won't
have a conflict with the built-in Round function should you upgrade to
Access 2000 or newer. The built in Round function uses "banker's" or
"scientific" rounding. It rounds .5 to the nearest even number instead of
always rounding up.

--
Wayne Morgan
Microsoft Access MVP


Helen said:
Hi Wayne, thanks very much for your reply, but this is where I need the
help. I did look at trying to round the values in the query (ie. before I
display them on the form where the addition is taking place) but don't see
any Round function in Access 97. I'm using Access 97 with Win NT 4. Any
suggestions would be gratefully appreciated?
Thanks, Helen


by Excel.
Click to expand...
unsettled
to add
up!
Click to expand...
Click to expand...
 
Hi Wayne,

Just a quick note to let you know that I've put your code in place (as is),
and am calling the function from the query behind my form and a couple of
the text boxes where there was a similar issue as well. It works a treat :-)

Many thanks again, I really appreciate your help.

Best regards, Helen



Wayne Morgan said:
Create your own round function in a module. Example for 2 decimals:

Public Function MyRound(curNumber As Currency) As Currency
Dim curRounded As Currency
curRounded = Int(curNumber * 100 + 0.5) / 100
MyRound = curRounded
End Function

Then, in your query, you would call the function in a calculated field.

RoundField1: MyRound([Table1].[Field1])

Once you do this, this field will not be updateable.

Name the function MyRound or something other than Round so that you won't
have a conflict with the built-in Round function should you upgrade to
Access 2000 or newer. The built in Round function uses "banker's" or
"scientific" rounding. It rounds .5 to the nearest even number instead of
always rounding up.

--
Wayne Morgan
Microsoft Access MVP


Helen said:
Hi Wayne, thanks very much for your reply, but this is where I need the
help. I did look at trying to round the values in the query (ie. before I
display them on the form where the addition is taking place) but don't see
any Round function in Access 97. I'm using Access 97 with Win NT 4. Any
suggestions would be gratefully appreciated?
Thanks, Helen


by Excel.
Click to expand...
unsettled
to add
up!
Click to expand...
Click to expand...
 
Back
Top