Rounding - Access 2000

[Abay]

I have fields defined as integer and byte with 2 decimal places with a
format of either "Fixed" or "General Number". Access either rounds the dec
place up or down depending on the value entered, the result - I cannot see
the true value of the field which is being used to record hours or part of
an hour people work, so when a fraction of an hour is input as in 2.66 it
needs to be seen exactly as entered and not rounded up to 3.00 as is
happening now. Tried Access help to no avail. Any help would be much
appreciated.

Abay

 

[Dirk Goldgar]

I have fields defined as integer and byte with 2 decimal places with
a format of either "Fixed" or "General Number". Access either rounds
the dec place up or down depending on the value entered, the result -
I cannot see the true value of the field which is being used to
record hours or part of an hour people work, so when a fraction of
an hour is input as in 2.66 it needs to be seen exactly as entered
and not rounded up to 3.00 as is happening now. Tried Access help to
no avail. Any help would be much appreciated.

An Integer field is just what it says it is: an integer. That means it
can only hold whole numbers -- no fractions. Long Integer and Byte
fields are also "integer" fields; it's just that these three different
field sizes can hold different maximum sizes of integers.

You need to use a Single or Double field, depending on the number of
digits of precision you require.

 

[Abay]

Many thanks and please excuse my ignorance ..

Abay

 

[peregenem]

You need to use a Single or Double field, depending on the number of
digits of precision you require.

Data genuinely Single or Double in nature are rare. For me it is clear
the OP requires DECIMAL(n, 2). Jet's DECIMAL is a scaled integer type
and data beyond its scale are simply truncated i.e. effectively no
rounding at all:

CREATE TABLE Test
(data_col DECIMAL(8, 2) NOT NULL)
;
INSERT INTO Test VALUES (2.669)
;
SELECT data_col FROM Test
;
-- returns 2.66, data truncated.

 

[Dirk Goldgar]

Data genuinely Single or Double in nature are rare. For me it is clear
the OP requires DECIMAL(n, 2). Jet's DECIMAL is a scaled integer type
and data beyond its scale are simply truncated i.e. effectively no
rounding at all:

CREATE TABLE Test
(data_col DECIMAL(8, 2) NOT NULL)
;
INSERT INTO Test VALUES (2.669)
;
SELECT data_col FROM Test
;
-- returns 2.66, data truncated.

It's possible that Abay needs a decimal field, but I don't think it's at
all clear from the post, and I'm inclined to doubt it. I did consider
that before posting. But although the example we were given had two
decimal places, I don't think that necessarily means that all entries
will have two decimal places. More important, it seems to me, is that
*whatever* is entered be as little distorted by the storage type as
possible. It's certainly true that Single and Double fields have the
potential to modify some of the low-order decimal places, depending on
what is entered, but my guess is that, for hours data, it's very
unlikely that those decimal places will be significant.

There's the added consideration that Jet has a few problems handling
with Decimal fields. When circumstances do call for fixed-decimal
precision, therefore, I tend to use the Currency type instead, when the
4 decimal places is supports are sufficient.

 

[peregenem]

More important, it seems to me, is that
*whatever* is entered be as little distorted by the storage type as
possible. It's certainly true that Single and Double fields have the
potential to modify some of the low-order decimal places, depending on
what is entered, but my guess is that, for hours data, it's very
unlikely that those decimal places will be significant.

When circumstances do call for fixed-decimal
precision, therefore, I tend to use the Currency type instead, when the
4 decimal places is supports are sufficient.

For me, I model it as it is in real (lowercase, no pun <g>) life. If
it's two decimal places then model as two decimal places. I like the
fact DECIMAL doesn't round; currency does implicit bankers rounding
which may not be appropriate for non-financial data. I don't get the
argument that if you need, say, to store your currency values to five
decimal places it suddenly becomes REAL (uppercase) data.

There's the added consideration that Jet has a few problems handling
with Decimal fields.

Groan! There's merely one problem: the cursor engine can't sort
negative DECIMAL values correctly. No big deal because sorting should
*always* be the final operation (proprietary features which return
different resultsets base on the ORDER BY clause are unrelational -
TOP N springs to mind - and should always be avoided), thus the sorting
can be performed successfully in the middleware e.g. a recordset's Sort
property.

I used to think there was another problem: the CDEC() function is
broken in Jet (Q225931) but then I realized the problem is non-existent
because Jet uses the DECIMAL data type natively e.g.

SELECT TYPENAME(2.66)

returns Decimal. That's why if DECIMAL did really live up to the bad
press it gets in these groups we'd all be in trouble. Fortunately, it's
merely unfounded propaganda

 

[Dirk Goldgar]

For me, I model it as it is in real (lowercase, no pun <g>) life. If
it's two decimal places then model as two decimal places.

I agree completely on this point. I just don't take Abay's post as a
real indication that the hours data he/she is handling is essentially
two decimal places.

I like the
fact DECIMAL doesn't round; currency does implicit bankers rounding
which may not be appropriate for non-financial data. I don't get the
argument that if you need, say, to store your currency values to five
decimal places it suddenly becomes REAL (uppercase) data.

That's not my argument.

Groan! There's merely one problem: the cursor engine can't sort
negative DECIMAL values correctly.

I'm certainly concerned about the reliability of any data type that (a)
was added recently to the database engine, and (b) has a known bug.
Sure, I can work around that bug, but it still leaves me uneasy.

No big deal because sorting should
*always* be the final operation (proprietary features which return
different resultsets base on the ORDER BY clause are unrelational -
TOP N springs to mind - and should always be avoided), thus the
sorting can be performed successfully in the middleware e.g. a
recordset's Sort property.

That's an interesting contention and worth exploring. Are you saying
that queries using TOP N + ORDER BY should be avoided? That seems
strange to me, but maybe I'm not understanding you correctly.

I used to think there was another problem: the CDEC() function is
broken in Jet (Q225931) but then I realized the problem is
non-existent because Jet uses the DECIMAL data type natively e.g.

SELECT TYPENAME(2.66)

returns Decimal.

I never noticed that before! I'd assumed that it would represent that
literal as Double. Did that change from Jet 3.5? It is reassuring
about the Decimal data type, though, with regard to the concern I
expressed above.

That's why if DECIMAL did really live up to the bad
press it gets in these groups we'd all be in trouble. Fortunately,
it's merely unfounded propaganda

As documented in KB articles, there were a number of other, relatively
minor problems in Access's handling of Decimal fields -- probably not
directly Jet problems -- in Access 2000 and in Access 2002, but I think
they have largely been fixed in Access 2003. I never ran into any of
them myself.

 

[peregenem]

That's an interesting contention and worth exploring. Are you saying
that queries using TOP N + ORDER BY should be avoided? That seems
strange to me, but maybe I'm not understanding you correctly.

It's a regular discussion e.g. try

http://groups.google.com/group/microsoft.public.sqlserver.server/msg/5fee13bf7d798635

I'm certainly concerned about the reliability of any data type that (a)
was added recently to the database engine, and (b) has a known bug.

Depends what you mean by 'recent'. DECIMAL has been in three release
versions of Access over five years. Jet 4.0 was a significant revision
and, security patches aside, in all probability the last ever version
because Jet has been 'depreciated' by Microsoft and its team long since
disbanded. Holding out for Jet 5.0 before using DECIMAL (and other
longed-for Jet 4.0 functionality) may be unrealistic.

 

[Dirk Goldgar]

It's a regular discussion e.g. try

http://groups.google.com/group/microsoft.public.sqlserver.server/msg/5fee13bf7d798635

Very interesting. That's something I was completely unaware of, and
something I'll have to think about. If a feature is very useful and
broadly implemented, I'm not sure whether its non-conformance to the
theoretical model is a breaker. But I need to think about this some
more. Thanks for pointing out the issue.

 

[peregenem]

If a feature is very useful and
broadly implemented, I'm not sure whether its non-conformance to the
theoretical model is a breaker.

'Broadly implemented' is not as good as 'ANSI-compliant'.

AFAIK TOP N is limited to Jet and T-SQL. Other SQLs have similar
functionality (e.g. LIMIT in MySQL) but no one says whether they have
implemented in the same way as Microsoft or any other vendor for that.
This kinda is the point of the ANSI standards: the same Standard
feature must be exposed in the same way by each ANSI-compliant product.

 

[Dirk Goldgar]

'Broadly implemented' is not as good as 'ANSI-compliant'.

AFAIK TOP N is limited to Jet and T-SQL.

That may be true. I just had a quick look around and couldn't find out
for sure about other SQL dialects. I'd thought the construct was more
widely supported than that.

On the other hand, I'm not sure that it makes a huge difference from a
practical point of view. This is certainly a debatable point, but if
I'm targeting a specific platform for development, and that platform
supports a non-standard feature that makes my development easier or
gives me a more efficient product, the non-standard nature of that
feature shouldn't prevent me from taking advantage of it.

Obviously, there are a number of considerations that may apply in such a
case, including (of course) how willing I am to be tied to a specific
platform, as well as how much I gain (on the targeted platform) by using
the non-standard feature instead of the standards-compliant approach.
It definitely helps to know that there *is* a standards-compliant means
to achieve the same end, because that means the non-standard solution
can be made to work on other platforms, if necessary, albeit at an
additional development cost.

 

[peregenem]

I'm not sure that it makes a huge difference from a
practical point of view. This is certainly a debatable point, but if
I'm targeting a specific platform for development, and that platform
supports a non-standard feature that makes my development easier or
gives me a more efficient product, the non-standard nature of that
feature shouldn't prevent me from taking advantage of it.

Posted in another Access group, here's another reason for avoiding TOP
N syntax:

Using SELECT TOP 5 [MyTable].partno works, but I need the TOP number as form
field input

I don't think the MS proprietary TOP N syntax is going to work e.g.

CREATE PROCEDURE TestCustomers
row_count INTEGER = 5)
AS
SELECT TOP :row_count CustomerID
FROM Customers
;

generates an error, 'The SELECT statement includes a reserved word or
an argument name that is misspelled or missing, or the punctuation is
incorrect.'

The following standard SQL construct works fine:

CREATE PROCEDURE TestCustomers
row_count INTEGER = 5)
AS
SELECT T1.CustomerID
FROM Customers AS T1
WHERE :row_count >= (
SELECT COUNT(*)
FROM Customers
WHERE T1.CustomerID <= CustomerID
)
;
EXECUTE TestCustomers
;
EXECUTE TestCustomers 3
;
EXECUTE TestCustomers 7
;
etc etc

 

[Dirk Goldgar]

I'm not sure that it makes a huge difference from a
practical point of view. This is certainly a debatable point, but if
I'm targeting a specific platform for development, and that platform
supports a non-standard feature that makes my development easier or
gives me a more efficient product, the non-standard nature of that
feature shouldn't prevent me from taking advantage of it.

Posted in another Access group, here's another reason for avoiding TOP
N syntax:

Using SELECT TOP 5 [MyTable].partno works, but I need the TOP number
as form field input

I don't think the MS proprietary TOP N syntax is going to work e.g.

CREATE PROCEDURE TestCustomers
row_count INTEGER = 5)
AS
SELECT TOP :row_count CustomerID
FROM Customers
;

generates an error, 'The SELECT statement includes a reserved word or
an argument name that is misspelled or missing, or the punctuation is
incorrect.'

The following standard SQL construct works fine:

CREATE PROCEDURE TestCustomers
row_count INTEGER = 5)
AS
SELECT T1.CustomerID
FROM Customers AS T1
WHERE :row_count >= (
SELECT COUNT(*)
FROM Customers
WHERE T1.CustomerID <= CustomerID
)
;
EXECUTE TestCustomers
;
EXECUTE TestCustomers 3
;
EXECUTE TestCustomers 7
;
etc etc

It's a good solution to a problem that can't be solved by the
non-standard feature, true; at least, not as that feature is
implemented. But the real question is whether to use the non-standard
feature when it does solve the problem.