Access-0.25 shows as zero in table-I want 0.25

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I get my number fields to display values that are less than 1 as the
actual value; e.g., 0.25?

They currently display as zero.

Thanks very much,
 
B said:
How do I get my number fields to display values that are less than 1
as the actual value; e.g., 0.25?

They currently display as zero.

Thanks very much,
Click to expand...

What Field Size did you specify? You cannot use Byte, Integer, or Long Integer
as by defeiniton, those do not support fractional values. You need to use
Single, Double, Decimal or use the DataType Currency.

Note that formatting is a completely separate issue and only affects display,
not storage.
 
On Sat, 7 Oct 2006 08:55:02 -0700, B A Sullivan <B A
How do I get my number fields to display values that are less than 1 as the
actual value; e.g., 0.25?
Click to expand...

By default, a Number field is defined as a Long Integer. Integers are,
by definition, whole numbers, and cannot accept fractional values such
as 0.25 or 1.25.

Either change the properties of the field - in the lower left corner
of the table design screen when you select the field - from Long
Integer to Float or Double; or change the datatype of the field itself
from Number to Currency. Float and Double are accurate to about 7 or
14 decimal places' accuracy, but both have problems with "roundoff
error"; Currency handles exactly four, no more and no fewer, decimal
places with no roundoff problems.

Depending on your version of Access, you may also have a Decimal size
property. Be aware that this has some bugs: see

http://support.microsoft.com/kb/837148/en-us

John W. Vinson[MVP]
 
John said:
Depending on your version of Access, you may also have a Decimal size
property. Be aware that this has some bugs: see

http://support.microsoft.com/kb/837148/en-us
Click to expand...

The sort order problem is an old chestnut: I don't think it's still
considered a 'bug' (at least I hope not) and is certainly no excuse to
avoid a data type which the engine itself uses natively (SELECT
TYPENAME(0.5) returns 'Decimal'). But now you've brought it up, the
main points are:

· the problem has been fixed in Access2007 engine;
· SQL is a set-based language where order has no meaning; it only
affects the ORDER BY clause which uses a cursor.

While it is annoying that the engine puts the correctly-ordered
positive values before the correctly-ordered negative values, it only
affects descending order sorting and can usually be done in the
middleware e.g. recordset.Sort method (in 'real' client-server setups
it is normal to delegate sorting to the client side anyhow).

Jamie.

--
 
· the problem has been fixed in Access2007 engine;
Click to expand...

which is still in beta (and should not be used for production use!)
and which many people do not have;
· SQL is a set-based language where order has no meaning; it only
affects the ORDER BY clause which uses a cursor.
Click to expand...

but which can affect real-life operations in Access applications.

Maybe having wrong results generated from commercial applications
isn't significant to you, but some people might find it so.

John W. Vinson[MVP]
 
John said:
but which can affect real-life operations in Access applications.
Click to expand...

The infamous incident on which the whole 'Access community against
DECIMAL' is based was a user being disappointed with the results of
this:

SELECT TOP 1 decimal_column
FROM MyTable

I trust the workaround here is obvious to anyone entrusted with the SQL
code of a commercial application (hint: replace the broken proprietary
syntax with something portable that works).
Maybe having wrong results generated from commercial applications
isn't significant to you, but some people might find it so.
Click to expand...

I think the only usage case that I know of that has any credibility is
where a report is based on a query so the query has to be sorted using
ORDER BY i.e. there is no recordset or other middleware with which to
perform the sort. Of course, if we're talking about a *commercial*
application I'd recommend the purchase of an industrial strength report
writer...<g>.

But if we are talking pragmatics rather than theory, how often do you
sort a decimal/single/double float column that contains negative
numbers in descending order where it cannot be done in middleware?

Jamie.

--
 
Back
Top