A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

rounding making result of calculations look incorrect



 
 
Thread Tools Display Modes
  #1  
Old May 25th, 2004, 02:57 PM
Helen
external usenet poster
 
Posts: n/a
Default rounding making result of calculations look incorrect

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



  #2  
Old May 25th, 2004, 03:45 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default rounding making result of calculations look incorrect

Round the input values before doing the addition and you'll get the value
you're after.

--
Wayne Morgan
Microsoft Access MVP


"Helen" wrote in message
...
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





  #3  
Old May 27th, 2004, 03:16 PM
Helen
external usenet poster
 
Posts: n/a
Default rounding making result of calculations look incorrect

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


"Wayne Morgan" wrote in message
...
Round the input values before doing the addition and you'll get the value
you're after.

--
Wayne Morgan
Microsoft Access MVP


"Helen" wrote in message
...
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







  #4  
Old May 27th, 2004, 04:18 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default rounding making result of calculations look incorrect

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" wrote in message
...
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


"Wayne Morgan" wrote in

message
...
Round the input values before doing the addition and you'll get the

value
you're after.

--
Wayne Morgan
Microsoft Access MVP


"Helen" wrote in message
...
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









  #5  
Old May 27th, 2004, 09:36 PM
Helen
external usenet poster
 
Posts: n/a
Default rounding making result of calculations look incorrect

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" wrote in message
...
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" wrote in message
...
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


"Wayne Morgan" wrote in

message
...
Round the input values before doing the addition and you'll get the

value
you're after.

--
Wayne Morgan
Microsoft Access MVP


"Helen" wrote in message
...
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











  #6  
Old May 29th, 2004, 12:58 PM
Helen
external usenet poster
 
Posts: n/a
Default rounding making result of calculations look incorrect

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" wrote in message
...
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" wrote in message
...
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


"Wayne Morgan" wrote in

message
...
Round the input values before doing the addition and you'll get the

value
you're after.

--
Wayne Morgan
Microsoft Access MVP


"Helen" wrote in message
...
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











  #7  
Old May 29th, 2004, 02:29 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default rounding making result of calculations look incorrect

You're welcome. Glad you got it going.

--
Wayne Morgan
MS Access MVP


"Helen" wrote in message
...
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:00 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.