Numbers in a query

[Guest]

This question may be in the wrong forum - I'm just not sure.
I have been ranking a field in my query and notice that some actual ties are
not recognized as such. Here is a short description that will show what I
mean.

A query has fields N1,N2,N3 (all single, 3 decimal places). A 4th field is
a calculated field, N4: N1 - N2 + N3. Here are two records.

N1 N2 N3 N4
5.555 6.666 7.777 6.6659998894
1.299 -6.001 -0.634 6.6660003662

Now, I can format these N4 values to look like 6.666 (the actual answer),
but they still rank differently. The tie is not recognized. I need both
answers to BE 6.666, not just look like 6.666.

How do I get around this. I can't change these into text because I need to
do other arithmetic operations on the values.

Thanks

 

[Guest]

Actually, if I add another field, I can get this to work, I think.

N5: INT(1000*N4 + .01)/1000 should always give a solution to 3 decimal
places, given inputs that are added/subtracted having 3 decimal places.

The .01 value is somewhat arbitrary at this point. Is there a more standard
way to do this?

 

[Guest]

AliasN5: Format([N5],"0.000")

Actually, if I add another field, I can get this to work, I think.

N5: INT(1000*N4 + .01)/1000 should always give a solution to 3 decimal
places, given inputs that are added/subtracted having 3 decimal places.

The .01 value is somewhat arbitrary at this point. Is there a more standard
way to do this?

 

[Guest]

I'm Sorry.... Here is what you are looking for:

N4: Format([N1] - [N2] + [N3], "0.000")

 

[John W. Vinson]

This question may be in the wrong forum - I'm just not sure.
I have been ranking a field in my query and notice that some actual ties are
not recognized as such. Here is a short description that will show what I
mean.

A query has fields N1,N2,N3 (all single, 3 decimal places). A 4th field is
a calculated field, N4: N1 - N2 + N3. Here are two records.

N1 N2 N3 N4
5.555 6.666 7.777 6.6659998894
1.299 -6.001 -0.634 6.6660003662

Now, I can format these N4 values to look like 6.666 (the actual answer),
but they still rank differently. The tie is not recognized. I need both
answers to BE 6.666, not just look like 6.666.

The problem is that you're apparenly using a Float datatype for tjhese
numbers. A Float value *is an approximation* - it's a binary fraction
times a scaling factor.

Just as 1/7 cannot be represented exactly in a decimal expansion
(0.142856142856142856....) so 6.666 cannot be represented EXACTLY in a
Float.

Change the datatypes of the N1, N2 and N3 fields from Float to either
Currency (which has four and exactly four decimal places and does not
suffer roundoff error) or Decimal.

John W. Vinson [MVP]

 

[John Spencer]

No guarantee this will work, but I would try CCur if you don't need
accuracy beyond four decimal places.
N4: CCur(N1 - N2 + N3)

If your calculation could ever return a null then
N4: CCur(Nz(N1 - N2 + N3,0))

Read elsewhere in the thread about the inaccuracy of floating point
numbers (Single and Double).



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Thanks for responding, but I think this would turn the result into a string
that I can't use for arithmetic operations.

I'm Sorry.... Here is what you are looking for:

N4: Format([N1] - [N2] + [N3], "0.000")

Actually, if I add another field, I can get this to work, I think.

N5: INT(1000*N4 + .01)/1000 should always give a solution to 3 decimal
places, given inputs that are added/subtracted having 3 decimal places.

The .01 value is somewhat arbitrary at this point. Is there a more standard
way to do this?

 

[Guest]

Thank you to John & John for your insights. For my own understanding, may I
ask a follow-up?

Based on your advice, I now use Data Type: Currency, Format: 0.000, Decimal
Places: 3 in my tbl.

In my query, I do mathematical operations (may include arithmetic, roots,
trig, etc..) on N1,N2,N3 to produce the calculated field N4, N5, N6.. . Is
the Currency Data Type always 'inherited'? If so, I should be OK just
using N4: (N1 - N2 +....) with a format property 0.000 instead of N4: CCur(N1
- N2 +....) .

 

[Guest]

I hate to be such a pest, but even when I use CCur as you've suggested I can
run into problems if I don't also use an input mask to restict the input to
no more than 3 decimal places. I'd like to offer the following comment.

I need the calculated field N4 to have exactly 3 decimal places with no
rounding. (If N4 computes to 5.4567, I not only need the value to look like
5.456, but I need it to in fact be 5.456) Using CCur and format 0.000, I can
make the numbers look OK, but later, when Ranking results, the value is still
'seen' as 5.4567.

It may be unconventional, but the only solution I can get to work everytime
with no input mask and where N1, N2, N3 can be any numerical type that
supports decimals is the one I mentioned in my 2nd posting: Using N5:
INT(1000*(N4 + .0001)/1000. This lops off any decimal places beyond the 3rd
without allowing rounding of the 3rd decimal place. (The '.0001' is somewhat
arbitrary, as long as I don't use a value as large as .5, causing the 3rd
decimal to round up when the 4th is 5 to 9.)
An input of 5.4567 not only displays as 5.456 but, for purposes of ranking,
is 'seen' as 5.456. It would Tie another value input as 5.456 (that's
actually the point that is causing me all this concern about decimal places)

 

[Jamie Collins]

I hate to be such a pest, but even when I use CCur as you've suggested I can
run into problems if I don't also use an input mask to restict the input to
no more than 3decimalplaces. I'd like to offer the following comment.

I need the calculated field N4 to have exactly 3decimalplaces with no
rounding.

Use DECIMAL(N, 3) where N is four or greater to reflect integer scale.
Values with more than three decimal places will be truncated.

Jamie.

--