Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access (Access 2000)

    I would use either the CLng (Convert to Long) or CInt (Convert to Integer) functions.

    Depends on how big you anticipate the answer to be. CLng would be the easy approach, I guess.

    -Mike

  2. #2
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rounding (Access 2000)

    Subject edited by HansV - again - to make it more descriptive than just "Access" - please give your posts a subject that tells what it is about.

    I have written a formula that multiplies two fields. The answer is correct, but I need to use the round function like I would in Excel. I need it to round to zero decimal places. How can I accomplish this task? Thank you.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Rounding (Access 2000)

    In Access you can use the VB/VBA Round() function, however, this function does not always return the same results that you get when using the Excel ROUND Worksheet function. This is explained in MSKB 194983:

    Round Function different in VBA 6 and Excel Spreadsheet

    As noted in article, "The VBA Round() function uses Banker's rounding while the spreadsheet cell function uses arithmetic rounding." For more details, see article. Also if interested see MSKB 196652:

    HOWTO: Implement Custom Rounding Procedures

    This article will tell you more about rounding numbers (including a detailed description of the distinction between "Banker's" and "Arithmetic" rounding) than you probably need (or want) to know, and provides examples of custom rounding functions. If you aren't fussy, just use the VBA Round() function, but be aware that it doesn't work the same way the Excel ROUND function does.

    HTH

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Access (Access 2000)

    Depending on the type of calculation, these functions may provide the desired results. However if using CInt or CLng functions, be aware of possible "floating point" issues as described in MSKB 195657:

    PRB: Type Conversion Functions Can Return Unexpected Results

    Brief excerpt:

    "This is caused by the way floating point values are handled in computers. Because most floating point values cannot be accurately represented with fixed length binary values, the internal result of the floating point calculation may differ slightly. For example, 25.0 * 0.1 may be 02.5000... 001 or 02.4999...999 depending on many factors. This rounding error causes the CInt function to return 2 or 3 in different situations."

    See article for more info, and links to other MSKB articles related to floating-point precision.

    HTH

Posting Permissions

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