Results 1 to 13 of 13
  1. #1
    BrittKnee is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    17

    Automatic Calculation Is Off By $0.01

    Hi!



    I have an automatically calculated field that is being thrown of by .01. This issue has not occurred in any previous entries, so I am completely stumped. Any insight is greatly appreciated. **Note: This calculation is invoice related so rounding down to the dollar is not an option.**

    In this case: $351,526.00 + $691,560.00 - $0.00 - $0.00 - ($691,560.00 * .8) = $489.838.00

    Adjusted Total Due: IIf([% Discount]<>0,([tolls due]+[fees due]-[removed_tolls]-[removed_fees])-([% Discount]*[fees due]-[removed_fees]),[tolls due]+[fees due]-[removed_tolls]-[removed_fees])

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,653
    Probably due to rounding? If any of those values are from variables or fields then possibly one or more are accurate to more than 2 decimal places. If they are all typed in, then I have no other idea except to suspect some code or other factor is affecting the output.
    I presume you're saying the above calculation is what the answer should be and that answer is $489,838.00 not $439.838.00
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  3. #3
    BrittKnee is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    17
    Correction: $489.837.99

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    909
    What are the data types of each field involved in the calc?

    The currency data type shouldn't suffer from floating point rounding issues, so hopefully all the fields involved are currency data types? If the discount % for example is a double/single instead, it may convert your calculations to a floating point number which may be causing the issue? Just a theory... If that's the case you could try using the CCur() function to convert the percent to a currency data type.

    For example
    Code:
    IIf(
        [% Discount]<>0 ,
        ([tolls due]+[fees due]-[removed_tolls]-[removed_fees])-(CCur([% Discount]*[fees due])-[removed_fees]) ,
        [tolls due]+[fees due]-[removed_tolls]-[removed_fees]
    )
    Last edited by kd2017; 08-10-2021 at 11:42 AM.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,653
    I agree with currency type but perhaps mistakenly assumed that's what was being used because of the $ signs.
    If that's the case you could try using the CCur()
    or multiply by 8 and divide by 100 - thus using 2 integers.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  6. #6
    BrittKnee is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    17
    Sorry for just getting back. I was on vacation. Unfortunately Ccur() did not work. I tried changing the data type to number in the table and keeping the currency format in the form and still no luck. I have tried all different other combos of numbers and yielded the correct result. I am completely stumped. Thanks for the suggestions.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,653
    Well, you could always post a zipped db copy here with just enough to replicate the issue. If you do, maybe state what we'd need to do to get the same result.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  8. #8
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,297
    There is not a data type Ďnumberí. That is a description to differentiate from text, date, currency, Boolean etc (would be better if it was described as Ďnumericí)

    So what type of number are you using? Byte, integer, long, double,single or decimal?

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,653
    Number is listed as a data type in all M$ documentation that I've ever read. Byte, integer, Long, etc. are field size properties.

    EDIT - sorry, forgot to say that if you have any links to information that states otherwise, would you be so kind as to post it?
    Thanks.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  10. #10
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,297
    I know, but it is insufficient for resolving problems such as this one. You can set the size of text but it does not have an impact on calculations. The number type does- otherwise why are there are the different c functions?

    Perhaps Iím being too pedantic but the information provided is insufficient to determine what is causing the problem

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,653
    I just wouldn't say there is no such thing as a data type of Number, that's all. You are obviously free to think otherwise but I will join you if you have any reliable info that says M$ documentation is wrong.

    To answer your question, I'd explain it thus: the number conversion functions are for converting/coercing a number to be compatible with the Field Type property when the field is of Data Type "number". My comment had little to do with solving the problem as much as it did for presenting a dissenting opinion about whether or not there is no Data Type of "number".
    Last edited by Micron; 08-19-2021 at 04:27 PM. Reason: clarification
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    909
    It might be worth a shot to investigate each individual number in the calculation, add decimal places to the formatting to see if something funny is going on. If any of these numbers are doubles or floats I'd change all of them to the currency data type.

  13. #13
    BrittKnee is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    17
    Sorry, I meant to reply back to this. I was able to resolve the issue. It was definitely caused by multiplying the %discount to the fees owed. Thanks for all of your help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Automatic Due Date Calculation?
    By mrjane in forum Access
    Replies: 4
    Last Post: 11-18-2015, 03:31 PM
  2. Automatic calculation of table field (A2003)
    By GraeagleBill in forum Programming
    Replies: 9
    Last Post: 04-20-2013, 12:06 PM
  3. Calculation Field in table vs Query calculation
    By Leonidsg in forum Database Design
    Replies: 18
    Last Post: 02-28-2013, 09:38 PM
  4. Replies: 17
    Last Post: 10-18-2012, 03:45 PM
  5. Automatic Calculation in table
    By musicalogist in forum Access
    Replies: 4
    Last Post: 04-22-2010, 11:52 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