Results 1 to 6 of 6
  1. #1
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45

    Round function producing erratic results

    I have a calculated field which uses the Round function.



    However when I query the field in a crosstab, most results are to two decimal places (as intended, by the Round function) but others are more than two.

    Any suggestions on why this is happening would be gratefully received.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What grouping / aggregate functions are you performing in the cross tab?
    Sometimes Access will pass the underlying value of a calculation to another query, I think it's something to do with implied data type conversions .
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    Thanks for replying Minty. I'm using the SUM function and grouping on location (in the rows) and month (in the columns).
    Click image for larger version. 

Name:	query.png 
Views:	9 
Size:	16.8 KB 
ID:	36434Click image for larger version. 

Name:	field.png 
Views:	9 
Size:	4.7 KB 
ID:	36435

  5. #5
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    Thanks Orange. I've seen that page but it's been no help with the issue I have

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    probably because your format property is Currency - which goes to 4 dp and your result type is double - perhaps you should use currency

    Also be aware that format properties mask actual values - and queries work off the actual value

    and finally calculated fields in tables have a limited range of options - so perhaps don't use a calculated field, do the calculation in your query

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

Similar Threads

  1. Sum Function Producing Error
    By chippy in forum Programming
    Replies: 9
    Last Post: 05-01-2018, 02:42 PM
  2. Round Function
    By chalupabatman in forum Queries
    Replies: 7
    Last Post: 08-18-2016, 07:43 AM
  3. Round Function
    By gcgoode in forum Access
    Replies: 2
    Last Post: 08-07-2015, 11:08 AM
  4. Replies: 4
    Last Post: 05-28-2015, 10:48 AM
  5. PDF or XPS button intermittently producing zoomed in results
    By kattatonic1 in forum Import/Export Data
    Replies: 1
    Last Post: 05-14-2014, 12:12 PM

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