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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|