Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rounding negative numbers (A2K SP-2)

    I have a frmCheckInEdit whose record source in qryRentCalc2. In the attached database, I have 2 records with receipts numbers 11004620 and 11004654. The first record was entered and then we wanted to reverse the transaction by entering the second record. By using minus days this keeps the occupancy rates calculations correct. The problem arises that the rounding routine rounds differently for positive and negative numbers which produced a 2 cents difference: one cent on the state sales tax calculation and one cent on the local sales tax calculation.

    Any suggestions for correcting the problem?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rounding negative numbers (A2K SP-2)

    In several places in qryRentCalc2, you round numbers to 2 decimals by multiplying by 100, adding 0.001, converting to long integer and dividing by 100. For negative numbers, you should not add, but subtract 0.001. This can be accomplished by multiplying all occurrences of 0.001 by Sgn([NumberOfDays]). The Sgn function returns 1 for positive numbers and -1 for negative numbers.

    A relatively fast way to do this is:
    - Switch to SQL view.
    - Copy the SQL to the clipboard.
    - Paste into any text editor (Notepad, Word, ...)
    - Replace 0.001 by 0.001*Sgn([NumberOfDays])
    - Select all and copy to the clipboard.
    - Paste into the SQL view.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding negative numbers (A2K SP-2)

    Many thanks. Your solution worked like a charm.

    Tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •