My industry require results to be to a preciscion of 6 decimal pl.

[DaveWall]

My industry require results to be to a preciscion of 6 decimal places. Access
handles 1.012345 but returns 1. if there is no moderator ie result is
1.000000 displays 1.

How can you force Access / Excel to display the requisite number of decimal
places?

For input, you can convert to a val. but if you need the reult of a fomula
to display, you do not have the ability to convert to a val.

Anyone know how to deal with this?

 

[Allen Browne]

In your table, you need to create a field of type Number, and in the lower
pane set the Field Size to Double.

This will give you around 15 significant digits.

If you want the field to always display 6 decimal places, you can set these
properties in the lower pane:
Format Fixed
Decimal Places 6

If you have a calculated field in a query, you can force it to a double by
wrapping the expression in CDbl(). Then when you display it in a form or
report, set the Format and Decimal Places properties of the text box.

 

[Jeff Boyce]

Dave

Take a look at the Format property. I may be misremembering, but maybe
something like:

#.000000

 

[Jamie Collins]

In your table, you need to create a field of type Number, and in the lower
pane set the Field Size to Double.

This will give you around 15 significant digits.

If you want the field to always display 6 decimal places, you can set these
properties in the lower pane:
Format Fixed
Decimal Places 6

If the number of decimal places was four and the industry in question
was 'finance' would you still suggest using Double/FLOAT for storage
and merely formatting any results? In my part of the world, such
accounting practise is illegal.

The data type the OP requires is DECIMAL. Yes, I know about the Jet
bug that prevents sorting negative decimal values in descending order
(KB 837148) but this has never caused me problems in practice. If it
is an issue for the OP, purchasing a more capable DBMS would be a
better option than flouting an industry requirement (and possibly
doing porridge as a result).

Jamie.

--

 

[Allen Browne]

Hmm. It is certainly a practical problem for me.

The user who originally brought this flaw to light was summing a DECIMAL
field to choose the maximum value (e.g. best sales for a period), and Access
gives the wrong answer, i.e. it picks the wrong one as the TOP value because
the sorting is wrong.

 

[Jamie Collins]

Hmm. It is certainly a practical problem for me.

The user who originally brought this flaw to light was summing a DECIMAL
field to choose the maximum value (e.g. best sales for a period), and Access
gives the wrong answer, i.e. it picks the wrong one as the TOP value

Still not a problem in practice. Using the example in

http://support.microsoft.com/?id=837148

but without creating an index (IIRC this didn't work for me anyhow).

The following

SELECT MAX(FldNumber) FROM TestTbl;

returns the expected result of 20.

Perhaps you mean the TOP N syntax? The following:

SELECT TOP 2 FldNumber FROM TestTbl;

returns 20 and -10, clearly incorrect. However, I wouldn't use MS
proprietary syntax where a simple subquery would do:

SELECT T1.FldNumber FROM TestTbl AS T1
WHERE 2 >= (
SELECT COUNT(*) FROM TestTbl AS T2
WHERE T1.FldNumber <= T2.FldNumber
);

returns 20 and 10, being the expected results.

Jamie.

--

 

[Allen Browne]

Jamie, if you have not experienced the problem:

Create a table named BadSort. No index.
One field of type Number, size Decimal, name MyDecimal
Enter Records:
-1
1
-2
7
0

Query:
SELECT TOP 1 BadSort.MyDecimal FROM BadSort
ORDER BY BadSort.MyDecimal DESC;

Result:
-1

That very simple example illustrates how the Decimal type completely
unusable in JET.

You can download the example from:
http://members.iinet.net.au/~allenbrowne/bug-08.html

 

[Jamie Collins]

Jamie, if you have not experienced the problem:

Create a table named BadSort. No index.
One field of type Number, size Decimal, name MyDecimal
Enter Records:
-1
1
-2
7
0

Query:
SELECT TOP 1 BadSort.MyDecimal FROM BadSort
ORDER BY BadSort.MyDecimal DESC;

Result:
-1

That very simple example illustrates how the Decimal type completely
unusable in JET.

Allen, Did you read my last post? I covered (and countered) your
points there using the MS example.

But, if you insist, let's go through it again using *your* example.

The fact the proprietary TOP N syntax doesn't work with DECIMAL is
just another nail in the coffin of the TOP N syntax. As I previously
pleaded, why use proprietary syntax when a simple subquery will do?
Here it is using you example:

SELECT T1.MyDecimal
FROM BadSort AS T1
WHERE 1 >= (
SELECT COUNT(*) FROM BadSort AS T2
WHERE T1.MyDecimal <= T2.MyDecimal
);

which gives the correct answer of 7.

Remember that SQL is a set-based language where row order has no
meaning, therefore the inability to sort negatives in the result set
is merely an inconvenience for the front end. Such inconvenience does
not render a standard data type 'completely unusable', as you claim.

This has been useful, though, because it is a great example of how the
TOP N syntax relies on sort order, is not set-based and therefore is a
flawed implementation for SQL. I'd heard it was based on a cursor
under the covers and here is the proof. If anyone reading this is
using TOP N, be warned.

Jamie.

--