Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Rounding - Access 2000

Discussion in 'Information Technology' started by Abay, Oct 28, 2005.

  1. Abay

    Abay
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Abay" <abcd@aol.com> wrote in message
    news:uh%23Wa622FHA.892@TK2MSFTNGP10.phx.gbl
    > 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.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  4. Abay

    Abay
    Expand Collapse
    Guest

    Many thanks and please excuse my ignorance ..

    Abay

    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:%23xGs2$22FHA.3296@TK2MSFTNGP09.phx.gbl...
    > "Abay" <abcd@aol.com> wrote in message
    > news:uh%23Wa622FHA.892@TK2MSFTNGP10.phx.gbl
    >> 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.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  5. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Dirk Goldgar wrote:
    > > when a fraction of
    > > an hour is input as in 2.66 it needs to be seen exactly as entered
    > > and not rounded

    >
    > 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.
     
  6. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    <peregenem@jetemail.net> wrote in message
    news:1130486776.972658.311030@g14g2000cwa.googlegroups.com
    > Dirk Goldgar wrote:
    >>> when a fraction of
    >>> an hour is input as in 2.66 it needs to be seen exactly as entered
    >>> and not rounded

    >>
    >> 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.


    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.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  7. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Dirk Goldgar wrote:
    > 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 :)
     
  8. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    <peregenem@jetemail.net> wrote in message
    news:1130515608.960623.295550@g47g2000cwa.googlegroups.com
    > Dirk Goldgar wrote:
    >> 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 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.

    >> 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.


    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.


    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  9. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Dirk Goldgar wrote:
    > > 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)

    >
    > 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.
     
  10. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    <peregenem@jetemail.net> wrote in message
    news:1130762669.810615.147610@g43g2000cwa.googlegroups.com
    > Dirk Goldgar wrote:
    >>> 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)

    >>
    >> 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

    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.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  11. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Dirk Goldgar wrote:
    > 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.
     
  12. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    Re: [OT] Rounding - Access 2000

    <peregenem@jetemail.net> wrote in message
    news:1130774635.766221.279760@g43g2000cwa.googlegroups.com
    > Dirk Goldgar wrote:
    >> 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.


    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.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  13. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Dirk Goldgar wrote:
    > 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
     
  14. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    Re: Rounding - Access 2000 [OT]

    <peregenem@jetemail.net> wrote in message
    news:1131092873.320475.157460@g44g2000cwa.googlegroups.com
    > Dirk Goldgar wrote:
    >> 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.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     

Share This Page