Limit decimal places without format statement
in [Form]
|
From: JohnM77 via AccessMonster.com on 4 Nov 2009 14:59 I wish for my query to display two decimal places without truncating the original data. I've accomplished this goal by using the format function in the query, but this function converts the numeric data to string. Consequently, programmatically filtering a form (which displays the query) for numerical ranges fails, due to the values no longer being numeric. Is there a way to force two decimal places to be displayed without converting to a non-numeric datatype? Thanks, John -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200911/1
From: Dirk Goldgar on 4 Nov 2009 15:03 "JohnM77 via AccessMonster.com" <u53407(a)uwe> wrote in message news:9ea037cb1bd22(a)uwe... >I wish for my query to display two decimal places without truncating the > original data. I've accomplished this goal by using the format function in > the query, but this function converts the numeric data to string. > Consequently, programmatically filtering a form (which displays the query) > for numerical ranges fails, due to the values no longer being numeric. > > Is there a way to force two decimal places to be displayed without > converting > to a non-numeric datatype? How about using the Round() function? It should be available in Access versions 2000 and after, though I believe it took a service pack to make it available to queries in Access 2000. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: JohnM77 via AccessMonster.com on 4 Nov 2009 15:34 Thanks for your reply, Dirk. I just tried the ROund function like this, Plate Length: Round([Length],2) expecting it to round all Length values to two decimal places. Oddly, the result is displaying 13 decimal places and is definitely not rounding. One value, entered as 1.01 is displayed as 1.0999999046326. The field datatype in the original table is number/single. Dirk Goldgar wrote: >>I wish for my query to display two decimal places without truncating the >> original data. I've accomplished this goal by using the format function in >[quoted text clipped - 5 lines] >> converting >> to a non-numeric datatype? > >How about using the Round() function? It should be available in Access >versions 2000 and after, though I believe it took a service pack to make it >available to queries in Access 2000. > -- Message posted via http://www.accessmonster.com
From: JohnM77 via AccessMonster.com on 4 Nov 2009 15:42 Actual value should have been 1.00999999046326 in previous post. JohnM77 wrote: >Thanks for your reply, Dirk. I just tried the ROund function like this, > >Plate Length: Round([Length],2) > >expecting it to round all Length values to two decimal places. Oddly, the >result is displaying 13 decimal places and is definitely not rounding. One >value, entered as 1.01 is displayed as 1.0999999046326. The field datatype in >the original table is number/single. > >>>I wish for my query to display two decimal places without truncating the >>> original data. I've accomplished this goal by using the format function in >[quoted text clipped - 5 lines] >>versions 2000 and after, though I believe it took a service pack to make it >>available to queries in Access 2000. -- Message posted via http://www.accessmonster.com
From: Dirk Goldgar on 4 Nov 2009 16:12
"JohnM77 via AccessMonster.com" <u53407(a)uwe> wrote in message news:9ea087306c312(a)uwe... > Thanks for your reply, Dirk. I just tried the ROund function like this, > > Plate Length: Round([Length],2) > > expecting it to round all Length values to two decimal places. Oddly, the > result is displaying 13 decimal places and is definitely not rounding. One > value, entered as 1.01 is displayed as 1.0999999046326. The field datatype > in > the original table is number/single. Interesting. When I do this with Doubles, it comes out looking pretty good, but not with Singles. I suspect the problems have to do with the relative precisions of the data types. Try this: forget the Round function, and modify the Format and Decimal Places properties of the field in the query. In query design view, click on the field in the grid, then press Alt+Enter or click the toolbar button to open the property sheet for the field. Set the Format property to "Fixed" and the Decimal Places property to 2. That should cause the field values to be displayed by the queries with two decimal places, except when the field has the datasheet's focus. Does that serve your needs well enough? It doesn't round the actual value, but causes it to display as rounded. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |