Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Peakwalker is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    14

    Formatting numeric data in a calculated query to not show too many significant figure

    I have numeric data (single, with three decimal places) that I am using in a calculated query. I have found online that calculated queries do not preserve the formatting of the incoming data, and so because of the default 'float' formatting, my data ends up with a whole bunch of significant figures well beyond the three that there should be. Setting the query to be fixed with three decimal places, or using the round function, displays the data correctly on screen, but if I actually click on the cell the multiple decimal places are still there.



    I have been able to convert the data to a string format a couple of different ways, but I end up copying data into excel later and I don't want to spend all my time converting the data back to numbers there.

    I have also been successful by using the currency format on the data, however wherever I have no value, it returns an #error message. In my particular spreadsheet this happens often and onscreen it just looks like a mess. I have used the Nz() function but it ends up creating a zero value (0.00) in all the fields instead of just nothing.

    So my question is either: Is there another way of formatting my data to display just three significant figures, or is there someway I can format my currency formatted data to properly display null values?

    Thanks.

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Use the ROUND function to round it to three decimals. Then all that floating issues don't pertain.

  3. #3
    Peakwalker is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    14
    The Round function works only if I round to zero decimal places. If I try to specify a specific number of decimal places, it just doesn't do anything (the value displayed has the whole slew of significant figures).

    I found another thread that mentioned using the CDbl function after Round, i.e. Round(CDbl([Expression]),3) , which worked for rounding the data but creates another #error message wherever I have null values. And interestingly, using CSng to make it a single does not round the data but does give me the #error message on nulls.

    It feels like any option I try only ever gets me part way there.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    The Round function works only if I round to zero decimal places. If I try to specify a specific number of decimal places, it just doesn't do anything (the value displayed has the whole slew of significant figures).
    I have never found that to be the case. An expression like:
    Round([Expression],3)
    usually works for me.

  5. #5
    Peakwalker is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    14
    I've done some digging around online about the round function, and realizing that part of my problem is dealing with single and double fields which are floating point numbers. I think the currency setting for me will work, but now I'm stuck on the issue of solving the #error.

    Is there a way to use the Nz function, but have the 'value if null' return a null value? I don't want it to say zero which is the default, I just want nothing to appear. Is this do-able?

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Maybe try something like this:
    Expr1: IIf(IsNull([MyField]),Null,[MyField])

    That will return a Null if the field is Null, else the value as a number.

  7. #7
    Peakwalker is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    14
    I just tried that, but it hasn't made a difference.

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Can you post a small of your data, the SQL code of your query, and your exact calculated expression?
    If its easier, you can just post your database, but I won't be able to download it until tonight.

  9. #9
    Peakwalker is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    14
    This is the version without your more recent suggestion.
    CU_%_AQ370_ALL: Round(CCur((IIf([_ASSAY]![SAMPLE_NO] Not Like "*",[_ASSAY_standards]![CU_%_AQ370],[_ASSAY]![CU_PLOT]))),3)

  10. #10
    Peakwalker is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    14
    I've simplified the code a bit so you aren't swamped with irrelevant information, and I'm having trouble making your null suggestion work. I will post when I figure it out.

  11. #11
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You can show some of the data you are working with from your query, namely the:
    [_ASSAY]![SAMPLE_NO], [_ASSAY_standards]![CU_%_AQ370], and [_ASSAY]![CU_PLOT] fields.

  12. #12
    Peakwalker is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    14
    Sorry, I hope I haven't frustrated you. I was trying to boil things down a bit to keep things simpler.

  13. #13
    Peakwalker is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    14
    The sample number can be "sample_number". The values can be anything, #.###.

    I have a list of sample numbers with assay values. I have another table of different sample numbers (representing standards) with assay values. I have a table that creates a union between the two so I have a list of all sample numbers from both tables.
    My query is trying to determine if there is no sample number from the assay table (therefore representing a sample number from the standards table), then the copper value from the standards table is used. If there is a sample number in the assay table, then the value from the assay table is used. The data in both the assay and standards table are single with three decimal points.

  14. #14
    Peakwalker is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    14
    CU_%_ALL: Round(CCur(Iif(IsNull((IIf([_ASSAY]![SAMPLE_NO] Not Like "*", [_ASSAY_standards]![CU_%_AQ370]),([_ASSAY]![CU_PLOT])),Null,(IIf([_ASSAY]![SAMPLE_NO] Not Like "*", [_ASSAY_standards]![CU_%_AQ370]),([_ASSAY]![CU_PLOT]))),3)
    I can't figure out what's going wrong with this. This is as close as I can get to adapting your isnull suggestion but I keep getting errors.

  15. #15
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Can you please post a small grid/sample of your data with expected results?
    What I am looking for is some actual values, along with your expected results (and covering different situations where things might be NULL).

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

Similar Threads

  1. Replies: 11
    Last Post: 07-28-2012, 03:55 AM
  2. Only show numeric values
    By dr4ke in forum Queries
    Replies: 2
    Last Post: 07-22-2012, 05:09 AM
  3. formatting a text box to show all data
    By mejia.j88 in forum Reports
    Replies: 8
    Last Post: 03-02-2012, 10:10 AM
  4. Replies: 5
    Last Post: 12-10-2011, 11:49 AM
  5. Formatting a Calculated Field
    By e_lady333 in forum Queries
    Replies: 0
    Last Post: 03-17-2010, 06:11 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