Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85

    Percentage Not Coming out the same as calculator

    This is my formula:



    NBHalfPack: [BookPrice]-([BookPrice]*[PercentHalf])

    BookPrice is 39.63
    PercentHalf is 6%

    If I do this on the calculator I get 37.2522 (39.63*.06 = 2.3778) 39.63-2.3778 = 37.2522
    My formula returns 37.24750

    I don't know why the calculation works differently?

    Help - How can I fix this?

  2. #2
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Check your formula.
    I get the same both ways.

    Are you ding this in a query?

    Click image for larger version. 

Name:	Query1.PNG 
Views:	4 
Size:	8.0 KB 
ID:	12573Click image for larger version. 

Name:	query2.PNG 
Views:	4 
Size:	4.5 KB 
ID:	12574

    Dale

  3. #3
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    Yes the calculation is in a query - the 6% is in the field PercentHalf

    The BookPrice is calculated also - is that the problem? if so how do I fix it to be just 39.63

    BookPrice: IIf([WBCode]="03-N0051" Or [WBCode]="03-N0061",Null,(([PackPrice]/20)+5))
    Last edited by Rhubie; 05-31-2013 at 08:56 AM. Reason: added more definition

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    My first thought is that the actual stored value is different than what you see. For instance, the actual value might be 39.625 but the field is formatted to display 2 digits. Can you post the db here? Or at least click in that field in table view.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I merged your duplicate threads together since both had responses. Please don't post the same question twice.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    Yes that is what is happening - I changed the field to be formatted with 4 digits. The calculation is in the query - how do I post it? Is there a way to take the stored value of 39.625 and put it in a field as 39.63?

  7. #7
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Look at the Round() function.

    Round(expression [,numdecimalplaces])

    Round(field,2)


    Dale

  8. #8
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    I had a round function in the query calc - it gave me other problems - because quickbooks rounded differently - I have to be able to match the way that quickbooks calculates. I am using Access for calculating and reporting (since QB can not).

    Is there a way to store it in another field as 39.63? With 2 decimals - then the other calcs would work.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Search on bankers rounding, which is what Access does. That should turn up alternative functions.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    I already looked for that - I could not find a way to do this. I'm a beginner on coding - so the things that I found did not make sense nor could I get them to work.

    All the rounding did was cause me other issues with QB. I had all the rounding on my calcs - worked fine until I wanted to round in QB - This has been so frustrating - I don't understand why they can not calculate the same way.

    Someone suggested a text field - but my calculations are in a query.

  11. #11
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    I have been told that Access will calculate currency to 4 decimal places.
    If I remember correctly, I had this type of problem with a discount. Like $3.95 * 15%.

    I think I used round to 3 decimal places and had the result type as currency.
    I will look around to see if I can find what I did.

    EDIT: Could not find anything. The only thing I found was that I did not do any rounding until the very end then changed the result to currency and rounded with 2 decimal places.

    Dale

  12. #12
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    I tried setting to currency - it still stores as 39.625 -

    I have tried all of the different settings on the field in the query - it always seems to store it as 39.625

  13. #13
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    This may not help but is just FYI I saw from RuralGuy.

    http://www.techonthenet.com/access/f...eric/round.php

    Dale

  14. #14
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    I looked at something very similar - I think the problem is that it still stores it as 39.625 instead of 39.63 -

    Do you know if I change the number to a textfield - can I designate it as 2 decimal points with CSTR and then change it back to a number with the VAL() function?

    I have to get the number to stores as 39.63 - it seems to be the only way that the calcs work the same.
    Last edited by Rhubie; 05-31-2013 at 10:34 AM. Reason: added note

  15. #15
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    You can store it as text anyway you like.

    You can not specify decimal places as text.

    Dale

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Textbox Calculator
    By Randy in forum Access
    Replies: 10
    Last Post: 03-16-2019, 07:36 PM
  2. Button in form to open Windows Calculator
    By Aosmond in forum Forms
    Replies: 5
    Last Post: 01-15-2013, 06:17 PM
  3. Calculator
    By Azeez_Andaman in forum Access
    Replies: 1
    Last Post: 08-14-2011, 12:10 PM
  4. Excel calculator
    By warrigal in forum Database Design
    Replies: 0
    Last Post: 03-15-2011, 05:50 PM
  5. Calculator on the form?
    By Peljo in forum Access
    Replies: 0
    Last Post: 02-28-2008, 02:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums