Welcome to SPN

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

Sign Up Now!

Stop Rounding in the Table

Discussion in 'Information Technology' started by Jennifer Cali, Jul 28, 2006.

  1. Jennifer Cali

    Jennifer Cali
    Expand Collapse
    Guest

    Hi,
    I have a table that has hours worked with each client. I have the field
    (intHoursWorked) set to "Field Type: Number" and in the properties of that
    field I have: "Field Size: Long Integer", "Format: General Number", and
    "Decimal Places: 2".

    What am I doing wrong? When I go back to the table, the number always rounds
    (i.e.: 6.7 becomes 7) - same issue in the form.
    --
    Thank you! - Jennifer
     
  2. Loading...


  3. tina

    tina
    Expand Collapse
    Guest

    the Long Integer field size of your Number data type (in the table) is the
    problem. Long Integer, Integer, and Byte are whole numbers only. if you want
    to store decimal values, use the Single or Double field size.

    hth


    "Jennifer Cali" <JenniferCali@discussions.microsoft.com> wrote in message
    news:B4A71BCB-3B47-44EB-A424-23F165C65372@microsoft.com...
    > Hi,
    > I have a table that has hours worked with each client. I have the field
    > (intHoursWorked) set to "Field Type: Number" and in the properties of that
    > field I have: "Field Size: Long Integer", "Format: General Number", and
    > "Decimal Places: 2".
    >
    > What am I doing wrong? When I go back to the table, the number always

    rounds
    > (i.e.: 6.7 becomes 7) - same issue in the form.
    > --
    > Thank you! - Jennifer
     
  4. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    tina wrote:
    > if you want
    > to store decimal values, use the Single or Double field size.


    ....but if you want to store decimal values accurately, use the DECIMAL
    data type.

    Jamie.

    --
     
  5. datAdrenaline

    datAdrenaline
    Expand Collapse
    Guest

    Or "Currency" ... even if the value is not monetary. The Currency datatype
    is a FIXED POINT (aka: Scaled Integer) decimal number (4 dec places). It is
    considered a HIGHLY accurate datatype since it is not prone to representation
    errors that are inherent in FLOATING POINT numbers (note the range of
    floating data types .. there is NO zero!!!). Also, when doing math with
    FIXED POINT math is faster.

    Just my $0.02 worth!!!

    --
    Regards,
    Brent Spaulding
    datAdrenaline


    "Jamie Collins" wrote:

    >
    > tina wrote:
    > > if you want
    > > to store decimal values, use the Single or Double field size.

    >
    > ....but if you want to store decimal values accurately, use the DECIMAL
    > data type.
    >
    > Jamie.
    >
    > --
    >
    >
     
  6. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    datAdrenaline wrote:
    > > ....but if you want to store decimal values accurately, use the DECIMAL
    > > data type.

    >
    > Or "Currency" ... even if the value is not monetary. The Currency datatype
    > is a FIXED POINT (aka: Scaled Integer) decimal number (4 dec places). Itis
    > considered a HIGHLY accurate datatype since it is not prone to representation
    > errors that are inherent in FLOATING POINT numbers (note the range of
    > floating data types .. there is NO zero!!!). Also, when doing math with
    > FIXED POINT math is faster.


    Or DECIMAL, even if the value is monetary. The DECIMAL datatype is a
    FIXED POINT (aka: Scaled Integer) decimal number of up to 28 digits
    before the decimal and up to 28 digits after (scale) to a combined
    (precision) maximum of 38 digits. It is considered a HIGHLY accurate
    datatype since it is not prone to representation errors that are
    inherent in FLOATING POINT. It is even more accurate than CURRENCY, not
    just due to its greater scale and precision, but due to the fact
    CURRENCY exhibits banker's rounding, which is not always desireable
    with non-monetary data. Plus, no one is likely to automatically assume
    the value is monetary, which is much more likely with CURRENCY.

    > Just my $0.02 worth!!!


    Touché <g>!

    Jamie.

    --
     
  7. datAdrenaline

    datAdrenaline
    Expand Collapse
    Guest

    Yep ... that is all true ... however ... in MSAccess VBA the Decimal datatype
    is not supported directly ... you have to declare a variant, then use CDec()
    to coerce the variant into a decimal ... here is a clip from the help file of
    MSAccess 2003
    >>

    Note At this time the Decimal data type can only be used within a Variant,
    that is, you cannot declare a variable to be of type Decimal. You can,
    however, create a Variant whose subtype is Decimal using the CDec function
    <<
    Sure ... you can declare it at the table level ... but with the inability to
    use them directly in VBA and the inability to use them to sort (see:
    http://allenbrowne.com/bug-08.html) ... I typically would not choose that
    datatype, since it seems partially implemented, which seems to be the same
    sentiment that Allen Browne has.

    .... Just another stack of Format(CDec(0.02),"Currency") :)


    --
    Regards,
    Brent Spaulding
    datAdrenaline


    "Jamie Collins" wrote:

    >
    > datAdrenaline wrote:
    > > > ....but if you want to store decimal values accurately, use the DECIMAL
    > > > data type.

    > >
    > > Or "Currency" ... even if the value is not monetary. The Currency datatype
    > > is a FIXED POINT (aka: Scaled Integer) decimal number (4 dec places). It is
    > > considered a HIGHLY accurate datatype since it is not prone to representation
    > > errors that are inherent in FLOATING POINT numbers (note the range of
    > > floating data types .. there is NO zero!!!). Also, when doing math with
    > > FIXED POINT math is faster.

    >
    > Or DECIMAL, even if the value is monetary. The DECIMAL datatype is a
    > FIXED POINT (aka: Scaled Integer) decimal number of up to 28 digits
    > before the decimal and up to 28 digits after (scale) to a combined
    > (precision) maximum of 38 digits. It is considered a HIGHLY accurate
    > datatype since it is not prone to representation errors that are
    > inherent in FLOATING POINT. It is even more accurate than CURRENCY, not
    > just due to its greater scale and precision, but due to the fact
    > CURRENCY exhibits banker's rounding, which is not always desireable
    > with non-monetary data. Plus, no one is likely to automatically assume
    > the value is monetary, which is much more likely with CURRENCY.
    >
    > > Just my $0.02 worth!!!

    >
    > Touché <g>!
    >
    > Jamie.
    >
    > --
    >
    >
     
  8. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    datAdrenaline wrote:
    > in MSAccess VBA the Decimal datatype
    > is not supported directly ... you have to declare a variant, then use CDec()
    > to coerce the variant into a decimal


    First, remember that floating point and integers enjoy hardware
    support. Now, pretend you are you are a VBA6 manager tasked with
    implementing a scaled integer type with a precision of 38 digits. I
    think you would too come to the conclusion that the new Decimal type
    would best be implemented as a reference type using multiple integers
    i.e. the integers would not be required to exists in contiguous memory
    addresses.

    > inability to
    > use them directly in VBA


    So if you understand why Decimal was best implemented as a reference
    type, why is it such a big deal that is a Variant subtype, rather than
    an intrinsic type i.e. what practical difference does it make?

    Who says a Jet type need to map directly to an intrinsic VBA value type
    anyhow? VARCHAR, MEMO, OLEOBJECT, MEMO and BINARY are distinct Jet data
    types but they all map to the VBA String intrinsic type. TINYINT maps
    to Long but SMALLINT maps to Integer - explain that one <g>. The best
    example is CHAR(N): it map directly to the intrinsic value data type
    String * N, yet hardly anyone here uses either the Jet type or the VBA
    type.

    I trust you understand my suspicion that you are merely throwing up a
    smoke screen.

    > the inability to
    > use them directly in VBA and the inability to use them to sort (see:
    > http://allenbrowne.com/bug-08.html) ... I typically would not choose that
    > datatype, since it seems partially implemented, which seems to be the same
    > sentiment that Allen Browne has.


    With respect, I think you haven't done any research. I get the
    impression you read Allen Browne's biased attempt to discredit the
    DECIMAL type, which was something to do with the lack of support in
    DAO, I guess (but really no one, even Allen, can remember because he
    only spent an hour on it half a decade ago and has not got around to
    revisiting it since) and took the ideas as your own.

    If you link to the article and say things like 'inability to use them
    to sort', can I take it you are prepared to defend those points? For
    example:

    · How can something with a predictable (albeit wrong) sort order be
    rationally considered 'wildly inaccurate'?

    · Why say 'Nulls and zeros sort unpredictably' when they are entirely
    predicable and consistent e.g. can you post some code where the DECIMAL
    type violates Jet's strict collation that guarantees NULLs are sorted
    to the end of the resultset?

    · Are you aware that a sort is only wrong (but still predictable) in
    very limited circumstances i.e. when the order is descending AND the
    resultsets includes negative numbers AND the sort is performed by the
    engine (being more of a convenience rather than a show stopper e.g. use
    the recordset's Sort method)?

    > > > [CURRENCY] is a FIXED POINT
    > > > It is
    > > > considered a HIGHLY accurate datatype since it is not prone to representation
    > > > errors that are inherent in FLOATING POINT numbers
    > > > Also, when doing math with
    > > > FIXED POINT math is faster.


    Finally, my usual closing question. You have convinced us of the need
    for a fixed point type, so what do you do when the client demands five
    decimal places? Use CURRENCY and multiply by ten? Roll your own scaled
    integer type with a full set of (fast) mathematic functions?

    Jamie.

    --
     
  9. datAdrenaline

    datAdrenaline
    Expand Collapse
    Guest

    >>> So if you understand why Decimal was best implemented as a reference type
    <<<

    I don't (at least with a comfortable degree of confidence), But I am always
    willing and eager to learn!! (I ASSUME its along the same lines as to why a
    memo type is basically a pointer to an addres that begins a huge block of
    information) ... It is quite obvious that your knowledge level on the topic
    of data types and there implementation goes much deeper than mine ... :) ...
    but that does not mean that my advice to utilize the currency datatype is
    incorrect or undesireable. On the flip side, your defense of the decimal
    data type generates no disagreement from me. But, lets remember that the OP
    is keeping track of Hours worked, which I hardly beleive will require a 12
    Byte 38 digit precision! ... In that regard I'm sure it will not require the
    full consumption of an 8 byte currency type either!... The field could
    probably be declared as an Integer (2-bytes) and record the value in minutes,
    or as a "psuedo scaled-integer" via the format/input mask (ie: the user input
    16, but display the value as 1.6) ... if the "psuedo scaled-integer" approach
    I just mentioned would be used, you could probably even use the Number/Byte
    type!

    As indicated above, my depth of knowledge with respect to the interworking
    and implementaion of data types/hardware support etc. does not equal yours.
    I am, at most, a skilled amateur who's technical world is dominated by
    MSAccess/VBA/SQL Server and Human Machine Interfaces/OPC servers ... heck ...
    I can't even create a web page!!! But I can do some killer stuff in
    MSAccess!! Ultimately I can honestly say that I know a bunch of stuff, but I
    definately have a lot to learn. With discussions like this, I beleive we all
    benefit. You, me and the readers get a chance to exercise our minds by
    putting what we know in a post for the world to see. Then, as information
    is shared, it is absorbed into those minds that want to learn. Then, those
    who gain more knowledge, can help others ... you know ... the "Pay it
    Forward" concept.

    >> Who says a Jet type need to map directly to an intrinsic VBA value type <<


    They don't have to ... but the more they "line up" the easier it is to
    create your code ... at least until you know how the data types match up ...
    after all in table design (remember I am MSAccess centered!) the "Yes/No"
    datatype is a "Boolean" in VBA ... not a direct match, from a text
    description point of view!!

    >> I trust you understand my suspicion that you are merely throwing up a

    smoke screen. <<
    ... and ..
    >> I think you haven't done any research. I get the

    impression you read Allen Browne's biased attempt to discredit the
    DECIMAL type <<
    ... and ..
    >> and took the ideas as your own <<


    Despite the fact you indicated you stated the above "respectfully" ... it
    does not seem respectful to make the assumption that I hiding behind a smoke
    screen; can not form my own opinions/preferences; nor perform any "research".
    Please accept that I, in know way intended, nor intend, to "put up a smoke
    screen"!! If I was trying build a "smoke screen" I would NOT have linked to
    the article! I would have merely cut/paste with out reference to anyone! If
    I did not do research, then I would not have found Mr. Brownes' article! Nor
    is my info an attempt to discredit the DECIMAL data type. My info stated MY
    lack of desire to use that data type and why I have that view point. Which,
    hopefully, will allow the OP to make an informed/educated decision before
    they use it ... I, like you, present the information I have knowledge of ...
    Was my "research" "deep enough"? It was at the time I found it! <g> The
    depth of my research satisfied my curiousity for the given subject .. but
    apparently not deep enough to equal the knowledge you, and others I assume,
    possess, thus your reply to help improve a readers (including me) knowledge.
    I am always eager to learn more information about all this stuff, which
    ultimately will form my opinions and techniques while developing apps, and I
    truly appreciate your willingness to share your information, but I was a
    little taken back with the assumptions made towards me. I respect the
    knowledge that you have displayed in this thread and if my verbage and style
    of presentation in my responses has come accross as "my opinion is superior
    to yours" I apologize ... my intent with participating in public
    forums/groups is merely to expand & share my knowledge ... if what I share
    with others is INCORRECT or INCOMPLETE, then I HOPE other folks, like you, to
    jump in and educate the readers and me .... thats why we are here!! .... "Pay
    It Forward!"

    >> so what do you do when the client demands five decimal places? Use CURRENCY and multiply by ten? Roll your own scaled integer type with a full set of (fast) mathematic functions? <<


    Nope ......... I'd use the DECIMAL data type ..... <g> .... There is this
    person named Jamie on the boards that can help me with implementation! ....


    --
    Kind Regards,
    Brent Spaulding
    datAdrenaline


    "Jamie Collins" wrote:

    > datAdrenaline wrote:
    > > in MSAccess VBA the Decimal datatype
    > > is not supported directly ... you have to declare a variant, then use CDec()
    > > to coerce the variant into a decimal

    >
    > First, remember that floating point and integers enjoy hardware
    > support. Now, pretend you are you are a VBA6 manager tasked with
    > implementing a scaled integer type with a precision of 38 digits. I
    > think you would too come to the conclusion that the new Decimal type
    > would best be implemented as a reference type using multiple integers
    > i.e. the integers would not be required to exists in contiguous memory
    > addresses.
    >
    > > inability to
    > > use them directly in VBA

    >
    > So if you understand why Decimal was best implemented as a reference
    > type, why is it such a big deal that is a Variant subtype, rather than
    > an intrinsic type i.e. what practical difference does it make?
    >
    > Who says a Jet type need to map directly to an intrinsic VBA value type
    > anyhow? VARCHAR, MEMO, OLEOBJECT, MEMO and BINARY are distinct Jet data
    > types but they all map to the VBA String intrinsic type. TINYINT maps
    > to Long but SMALLINT maps to Integer - explain that one <g>. The best
    > example is CHAR(N): it map directly to the intrinsic value data type
    > String * N, yet hardly anyone here uses either the Jet type or the VBA
    > type.
    >
    > I trust you understand my suspicion that you are merely throwing up a
    > smoke screen.
    >
    > > the inability to
    > > use them directly in VBA and the inability to use them to sort (see:
    > > http://allenbrowne.com/bug-08.html) ... I typically would not choose that
    > > datatype, since it seems partially implemented, which seems to be the same
    > > sentiment that Allen Browne has.

    >
    > With respect, I think you haven't done any research. I get the
    > impression you read Allen Browne's biased attempt to discredit the
    > DECIMAL type, which was something to do with the lack of support in
    > DAO, I guess (but really no one, even Allen, can remember because he
    > only spent an hour on it half a decade ago and has not got around to
    > revisiting it since) and took the ideas as your own.
    >
    > If you link to the article and say things like 'inability to use them
    > to sort', can I take it you are prepared to defend those points? For
    > example:
    >
    > · How can something with a predictable (albeit wrong) sort order be
    > rationally considered 'wildly inaccurate'?
    >
    > · Why say 'Nulls and zeros sort unpredictably' when they are entirely
    > predicable and consistent e.g. can you post some code where the DECIMAL
    > type violates Jet's strict collation that guarantees NULLs are sorted
    > to the end of the resultset?
    >
    > · Are you aware that a sort is only wrong (but still predictable) in
    > very limited circumstances i.e. when the order is descending AND the
    > resultsets includes negative numbers AND the sort is performed by the
    > engine (being more of a convenience rather than a show stopper e.g. use
    > the recordset's Sort method)?
    >
    > > > > [CURRENCY] is a FIXED POINT
    > > > > It is
    > > > > considered a HIGHLY accurate datatype since it is not prone to representation
    > > > > errors that are inherent in FLOATING POINT numbers
    > > > > Also, when doing math with
    > > > > FIXED POINT math is faster.

    >
    > Finally, my usual closing question. You have convinced us of the need
    > for a fixed point type, so what do you do when the client demands five
    > decimal places? Use CURRENCY and multiply by ten? Roll your own scaled
    > integer type with a full set of (fast) mathematic functions?
    >
    > Jamie.
    >
    > --
    >
    >
     
  10. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    datAdrenaline wrote:
    > Despite the fact you indicated you stated the above "respectfully" ... it
    > does not seem respectful to make the assumption that I hiding behind a smoke
    > screen; can not form my own opinions/preferences; nor perform any "research".
    > Please accept that I, in know way intended, nor intend, to "put up a smoke
    > screen"!!


    The way I see it is there are some people who have chosen not to use
    DECIMAL because of Mr Browne's article and would encourage others to do
    the same. Apologies for making assumptions about your allegiances.

    As you can tell, I have some problems with that short article: it
    contains inaccuracies ("Nulls and zeros sort unpredictably"), emotive
    language that suggests a prejudiced view ("sorting is wildly
    inaccurate"), several minor issues thrown in 'mud slinging' style ("VBA
    has no Decimal data type"), plus one comment that would be totally
    irrelevant even if it were true ('[Creating an index] will not get you
    out of trouble if you ever need a query to perform aggregation on the
    field (such as summing)", but why would a set function be affected by a
    'sort order' bug when sets have no inherent order?)

    I have a lot of respect for Mr Browne but I do think he made an error
    of judgement in this case. He has not replied to any of my posts asking
    him to defend the article, to which he and others continue to link in
    these groups. If the author will not reconsider the article, I can only
    try and influence the people who link to the article by asking them to
    defend its contents.

    The general trend seems to be that when individuals look at the issues
    they conclude that the DECIMAL type is worthy of consideration.

    Apologies if I came down heavy. I may be guilty of the 'throw enough
    mud back' approach <g> ...

    > I can honestly say that I know a bunch of stuff, but I
    > definately have a lot to learn. With discussions like this, I beleive we all
    > benefit. You, me and the readers get a chance to exercise our minds by
    > putting what we know in a post for the world to see. Then, as information
    > is shared, it is absorbed into those minds that want to learn. Then, those
    > who gain more knowledge, can help others ... you know ... the "Pay it
    > Forward" concept.


    Amen. I would rather have a reasoned discussion about the article but,
    unlike you, the parties involved don't aren't interested reasoning, it
    seems :(

    Jamie.

    --
     
  11. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    Well, I don't know, maybe I'm strange but when I issue a SQL command where
    I specify the sort order I expect the results to come back in the order I
    specify not in some other order.

    As ordering on the decimal datatype doesn't always return an ordered data
    set then AFAICS the decimal datatype is broken, it has been broken since
    it's first inception and I have no doubt that it will remain broken for
    evermore.


    --

    Terry Kreft


    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1153139550.091670.254750@b28g2000cwb.googlegroups.com...
    >
    > datAdrenaline wrote:
    > > Despite the fact you indicated you stated the above "respectfully" ...

    it
    > > does not seem respectful to make the assumption that I hiding behind a

    smoke
    > > screen; can not form my own opinions/preferences; nor perform any

    "research".
    > > Please accept that I, in know way intended, nor intend, to "put up a

    smoke
    > > screen"!!

    >
    > The way I see it is there are some people who have chosen not to use
    > DECIMAL because of Mr Browne's article and would encourage others to do
    > the same. Apologies for making assumptions about your allegiances.
    >
    > As you can tell, I have some problems with that short article: it
    > contains inaccuracies ("Nulls and zeros sort unpredictably"), emotive
    > language that suggests a prejudiced view ("sorting is wildly
    > inaccurate"), several minor issues thrown in 'mud slinging' style ("VBA
    > has no Decimal data type"), plus one comment that would be totally
    > irrelevant even if it were true ('[Creating an index] will not get you
    > out of trouble if you ever need a query to perform aggregation on the
    > field (such as summing)", but why would a set function be affected by a
    > 'sort order' bug when sets have no inherent order?)
    >
    > I have a lot of respect for Mr Browne but I do think he made an error
    > of judgement in this case. He has not replied to any of my posts asking
    > him to defend the article, to which he and others continue to link in
    > these groups. If the author will not reconsider the article, I can only
    > try and influence the people who link to the article by asking them to
    > defend its contents.
    >
    > The general trend seems to be that when individuals look at the issues
    > they conclude that the DECIMAL type is worthy of consideration.
    >
    > Apologies if I came down heavy. I may be guilty of the 'throw enough
    > mud back' approach <g> ...
    >
    > > I can honestly say that I know a bunch of stuff, but I
    > > definately have a lot to learn. With discussions like this, I beleive

    we all
    > > benefit. You, me and the readers get a chance to exercise our minds by
    > > putting what we know in a post for the world to see. Then, as

    information
    > > is shared, it is absorbed into those minds that want to learn. Then,

    those
    > > who gain more knowledge, can help others ... you know ... the "Pay it
    > > Forward" concept.

    >
    > Amen. I would rather have a reasoned discussion about the article but,
    > unlike you, the parties involved don't aren't interested reasoning, it
    > seems :(
    >
    > Jamie.
    >
    > --
    >
     
  12. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    D'accord
    I simply never have trusted anothing related to the decimal datatype (unless
    the data is from a mssql/oracle table)
    the variant datatype simply doesn't work for native jet fields

    Pieter

    "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    news:eK1LqAcqGHA.928@TK2MSFTNGP04.phx.gbl...
    > Well, I don't know, maybe I'm strange but when I issue a SQL command
    > where
    > I specify the sort order I expect the results to come back in the order I
    > specify not in some other order.
    >
    > As ordering on the decimal datatype doesn't always return an ordered data
    > set then AFAICS the decimal datatype is broken, it has been broken since
    > it's first inception and I have no doubt that it will remain broken for
    > evermore.
    >
    >
    > --
    >
    > Terry Kreft
    >
    >
    > "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    > news:1153139550.091670.254750@b28g2000cwb.googlegroups.com...
    >>
    >> datAdrenaline wrote:
    >> > Despite the fact you indicated you stated the above "respectfully" ...

    > it
    >> > does not seem respectful to make the assumption that I hiding behind a

    > smoke
    >> > screen; can not form my own opinions/preferences; nor perform any

    > "research".
    >> > Please accept that I, in know way intended, nor intend, to "put up a

    > smoke
    >> > screen"!!

    >>
    >> The way I see it is there are some people who have chosen not to use
    >> DECIMAL because of Mr Browne's article and would encourage others to do
    >> the same. Apologies for making assumptions about your allegiances.
    >>
    >> As you can tell, I have some problems with that short article: it
    >> contains inaccuracies ("Nulls and zeros sort unpredictably"), emotive
    >> language that suggests a prejudiced view ("sorting is wildly
    >> inaccurate"), several minor issues thrown in 'mud slinging' style ("VBA
    >> has no Decimal data type"), plus one comment that would be totally
    >> irrelevant even if it were true ('[Creating an index] will not get you
    >> out of trouble if you ever need a query to perform aggregation on the
    >> field (such as summing)", but why would a set function be affected by a
    >> 'sort order' bug when sets have no inherent order?)
    >>
    >> I have a lot of respect for Mr Browne but I do think he made an error
    >> of judgement in this case. He has not replied to any of my posts asking
    >> him to defend the article, to which he and others continue to link in
    >> these groups. If the author will not reconsider the article, I can only
    >> try and influence the people who link to the article by asking them to
    >> defend its contents.
    >>
    >> The general trend seems to be that when individuals look at the issues
    >> they conclude that the DECIMAL type is worthy of consideration.
    >>
    >> Apologies if I came down heavy. I may be guilty of the 'throw enough
    >> mud back' approach <g> ...
    >>
    >> > I can honestly say that I know a bunch of stuff, but I
    >> > definately have a lot to learn. With discussions like this, I beleive

    > we all
    >> > benefit. You, me and the readers get a chance to exercise our minds by
    >> > putting what we know in a post for the world to see. Then, as

    > information
    >> > is shared, it is absorbed into those minds that want to learn. Then,

    > those
    >> > who gain more knowledge, can help others ... you know ... the "Pay it
    >> > Forward" concept.

    >>
    >> Amen. I would rather have a reasoned discussion about the article but,
    >> unlike you, the parties involved don't aren't interested reasoning, it
    >> seems :(
    >>
    >> Jamie.
    >>
    >> --
    >>

    >
    >
     
  13. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    D'accord
    I simply never have trusted anothing related to the decimal datatype (unless
    the data is from a mssql/oracle table)
    the variant datatype simply doesn't work for native jet fields

    Pieter

    "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    news:eK1LqAcqGHA.928@TK2MSFTNGP04.phx.gbl...
    > Well, I don't know, maybe I'm strange but when I issue a SQL command
    > where
    > I specify the sort order I expect the results to come back in the order I
    > specify not in some other order.
    >
    > As ordering on the decimal datatype doesn't always return an ordered data
    > set then AFAICS the decimal datatype is broken, it has been broken since
    > it's first inception and I have no doubt that it will remain broken for
    > evermore.
    >
    >
    > --
    >
    > Terry Kreft
    >
    >
    > "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    > news:1153139550.091670.254750@b28g2000cwb.googlegroups.com...
    >>
    >> datAdrenaline wrote:
    >> > Despite the fact you indicated you stated the above "respectfully" ...

    > it
    >> > does not seem respectful to make the assumption that I hiding behind a

    > smoke
    >> > screen; can not form my own opinions/preferences; nor perform any

    > "research".
    >> > Please accept that I, in know way intended, nor intend, to "put up a

    > smoke
    >> > screen"!!

    >>
    >> The way I see it is there are some people who have chosen not to use
    >> DECIMAL because of Mr Browne's article and would encourage others to do
    >> the same. Apologies for making assumptions about your allegiances.
    >>
    >> As you can tell, I have some problems with that short article: it
    >> contains inaccuracies ("Nulls and zeros sort unpredictably"), emotive
    >> language that suggests a prejudiced view ("sorting is wildly
    >> inaccurate"), several minor issues thrown in 'mud slinging' style ("VBA
    >> has no Decimal data type"), plus one comment that would be totally
    >> irrelevant even if it were true ('[Creating an index] will not get you
    >> out of trouble if you ever need a query to perform aggregation on the
    >> field (such as summing)", but why would a set function be affected by a
    >> 'sort order' bug when sets have no inherent order?)
    >>
    >> I have a lot of respect for Mr Browne but I do think he made an error
    >> of judgement in this case. He has not replied to any of my posts asking
    >> him to defend the article, to which he and others continue to link in
    >> these groups. If the author will not reconsider the article, I can only
    >> try and influence the people who link to the article by asking them to
    >> defend its contents.
    >>
    >> The general trend seems to be that when individuals look at the issues
    >> they conclude that the DECIMAL type is worthy of consideration.
    >>
    >> Apologies if I came down heavy. I may be guilty of the 'throw enough
    >> mud back' approach <g> ...
    >>
    >> > I can honestly say that I know a bunch of stuff, but I
    >> > definately have a lot to learn. With discussions like this, I beleive

    > we all
    >> > benefit. You, me and the readers get a chance to exercise our minds by
    >> > putting what we know in a post for the world to see. Then, as

    > information
    >> > is shared, it is absorbed into those minds that want to learn. Then,

    > those
    >> > who gain more knowledge, can help others ... you know ... the "Pay it
    >> > Forward" concept.

    >>
    >> Amen. I would rather have a reasoned discussion about the article but,
    >> unlike you, the parties involved don't aren't interested reasoning, it
    >> seems :(
    >>
    >> Jamie.
    >>
    >> --
    >>

    >
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4231 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  14. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Terry Kreft wrote:
    > As ordering on the decimal datatype doesn't always return an ordered data
    > set then AFAICS the decimal datatype is broken, it has been broken since
    > it's first inception and I have no doubt that it will remain broken for
    > evermore.


    Tell me if I've interpreted correctly. One aspect of the DECIMAL data
    type is has a problem associated with negative values in descending
    order, not that I actually ever had a need to sort negative DECIMAL
    values into descending order and regardless of the fact that order has
    no meaning in a set-based language like SQL. I could work around the
    problem but what's the point, it's broken. I'll just have to turn down
    all business that demands accurate data to more than four decimal
    places or use an inaccurate FLOAT data type without telling them. Same
    for the YESNO data type: it has problems with null values in outer
    joins, not that a Boolean data type makes any sense in SQL's three
    value logic, that it's non-standard and non-portable, that I could
    instead use an INTEGER data type constrained as IN (0, 1)... That's two
    broken data types, at least, so Jet's broken. SQL Server Express,
    right?

    Another approach could be to determine whether Jet is the appropriate
    platform for the job, whether the DECIMAL data type is required (does
    my client need accurate data that only a fixed point type can provide?
    do they need more than four decimal places? which rounding algorithm?),
    whether the problem is likely to be encountered (do I need ordered sets
    in SQL? do I have negative decimal values in this app? do I need
    negative decimal values in descending order?) and what the workarounds
    are (can I sort effectively in the middleware/report writer/front end?
    in the limited situations where I need engine-based negative decimal
    values in descending order, can I generate a sort order column by
    coerce the decimal data to another type?)

    Jamie.

    --
     
  15. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Pieter Wijnen wrote:
    > the variant datatype simply doesn't work for native jet fields


    I don't understand your point.

    Jet doesn't have a 'variant' data type so you are presumably referring
    to VBA's Variant, which has an explicit Decimal sub type. The native
    Jet DECIMAL type certainly works with the Variant Decimal sub type.
    Please, then, explain you point.

    TIA,
    Jamie.

    --
     
  16. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Simply: Decimal is a variant type field in Access, not numeric

    Pieter


    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1153210468.688526.173720@m79g2000cwm.googlegroups.com...
    >
    > Pieter Wijnen wrote:
    >> the variant datatype simply doesn't work for native jet fields

    >
    > I don't understand your point.
    >
    > Jet doesn't have a 'variant' data type so you are presumably referring
    > to VBA's Variant, which has an explicit Decimal sub type. The native
    > Jet DECIMAL type certainly works with the Variant Decimal sub type.
    > Please, then, explain you point.
    >
    > TIA,
    > Jamie.
    >
    > --
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4282 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  17. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    Well that's an awful lot of presumption from a simple statement.

    I'll answer in-line.

    --

    Terry Kreft


    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1153209571.062377.156390@s13g2000cwa.googlegroups.com...
    >
    > Terry Kreft wrote:
    > > As ordering on the decimal datatype doesn't always return an ordered

    data
    > > set then AFAICS the decimal datatype is broken, it has been broken since
    > > it's first inception and I have no doubt that it will remain broken for
    > > evermore.

    >
    > Tell me if I've interpreted correctly. One aspect of the DECIMAL data
    > type is has a problem associated with negative values in descending
    > order,


    Well I'd prefer to say that sorting of the decimal datatype is broken and
    then qualify that statement if necessary.

    > not that I actually ever had a need to sort negative DECIMAL
    > values into descending order


    Then it's not been a problem for you.

    > and regardless of the fact that order has
    > no meaning in a set-based language like SQL.


    You're slightly wrong here; order has no significance when you are carrying
    out set based operations.

    >I could work around the
    > problem but what's the point, it's broken. I'll just have to turn down
    > all business that demands accurate data to more than four decimal
    > places or use an inaccurate FLOAT data type without telling them.


    Well that's entirely your decision, seems a bit drastic to me though.

    > Same
    > for the YESNO data type: it has problems with null values in outer
    > joins, not that a Boolean data type makes any sense in SQL's three
    > value logic, that it's non-standard and non-portable, that I could
    > instead use an INTEGER data type constrained as IN (0, 1)... That's two
    > broken data types, at least, so Jet's broken. SQL Server Express,
    > right?


    Not sure where you're coming from here, it appears that you want to
    interpret the capabilities of the Yes/No datatype on your own terms then
    declare it broken in light of those terms. I would question the honesty of
    that.

    >
    > Another approach could be to determine whether Jet is the appropriate
    > platform for the job, whether the DECIMAL data type is required (does
    > my client need accurate data that only a fixed point type can provide?
    > do they need more than four decimal places? which rounding algorithm?),
    > whether the problem is likely to be encountered (do I need ordered sets
    > in SQL? do I have negative decimal values in this app? do I need
    > negative decimal values in descending order?) and what the workarounds
    > are (can I sort effectively in the middleware/report writer/front end?
    > in the limited situations where I need engine-based negative decimal
    > values in descending order, can I generate a sort order column by
    > coerce the decimal data to another type?)


    In other words analyse the customers requirements? I would guess that you
    do this.

    >
    > Jamie.
    >
    > --
    >
     
  18. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Terry Kreft wrote:
    > Well that's an awful lot of presumption from a simple statement.


    I did presumed that a .co.uk domain would recognise playful sarcasm
    when they saw it <g>.

    > > Tell me if I've interpreted correctly. One aspect of the DECIMAL data
    > > type is has a problem associated with negative values in descending
    > > order,

    >
    > Well I'd prefer to say that sorting of the decimal datatype is broken and
    > then qualify that statement if necessary.


    Throw the baby out with the bath water, would you <g>?

    > > and regardless of the fact that order has
    > > no meaning in a set-based language like SQL.

    >
    > You're slightly wrong here; order has no significance when you are carrying
    > out set based operations.


    What else would you be doing in SQL? (Hint: The ORDER BY clause is part
    of a cursor and not a query; cursors convert a result set into a
    sequential file structure)

    > it appears that you want to
    > interpret the capabilities of the Yes/No datatype on your own terms then
    > declare it broken in light of those terms. I would question the honesty of
    > that.


    You are right to question. I should post something to back up my claim,
    right?

    http://groups.google.com/group/microsoft.public.access.queries/msg/518d9390ba8939fe

     
  19. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Pieter Wijnen wrote:
    > Simply: Decimal is a variant type field in Access, not numeric


    By 'Access', do you mean VBA? The Decimal variant subtype *is* numeric
    e.g.

    Sub test()
    Dim d As Variant
    d = CStr("One")
    MsgBox IsNumeric(d)
    d = CDec(1)
    MsgBox IsNumeric(d)
    End Sub

    Did you read what I wrote in this thread about Decimal being
    implemented as a reference type?

    > Simply: Decimal is a variant type field in Access, not numeric

    By 'Access', do you mean Jet? AFAIK Jet does not have a 'variant' data
    type. Jet's DECIMAL data type *is* numeric e.g.

    SELECT TYPENAME(0.5) AS data_type,
    IIF(ISNUMERIC(0.5), 'Is indeed numeric', 'Is not numeric')
    AS is_numeric
    FROM AnyTable;

    Jamie.

    --
     
  20. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    No, I would separate the baby from the bathwater and then dispose of the
    bathwater.

    There is a subtle difference between what you said and my phrasing.

    Yes/No - I now see your point I skated over the "outer join" bit in your OP,
    yes that is a bug.

    I don't understand the relevance of my past experience or indeed my future
    requirements in deciding whether sorting by the decimal datatype is broken.


    --

    Terry Kreft


    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1153219948.866849.19870@m79g2000cwm.googlegroups.com...

    Terry Kreft wrote:
    > Well that's an awful lot of presumption from a simple statement.


    I did presumed that a .co.uk domain would recognise playful sarcasm
    when they saw it <g>.

    > > Tell me if I've interpreted correctly. One aspect of the DECIMAL data
    > > type is has a problem associated with negative values in descend> >

    order,
    >
    > Well I'd prefer to say that sorting of the decimal datatype is broken and
    > then qualify that statement if necessary.


    Throw the baby out with the bath water, would you <g>?

    > > and regardless of the fact that order has
    > > no meaning in a set-based language like SQL.

    >
    > You're slightly wrong here; order has no significance when you are

    carrying
    > out set based operations.


    What else would you be doing in SQL? (Hint: The ORDER BY clause is part
    of a cursor and not a query; cursors convert a result set into a
    sequential file structure)

    > it appears that you want to
    > interpret the capabilities of the Yes/No datatype on your own terms then
    > declare it broken in light of those terms. I would question the honesty

    of
    > that.


    You are right to question. I should post something to back up my claim,
    right?

    http://groups.google.com/group/microsoft.public.access.queries/msg/518d9390ba8939fe

     
  21. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Terry Kreft wrote:
    > Yes/No - I now see your point I skated over the "outer join" bit in your OP,
    > yes that is a bug.


    You said up thread (quote), "As ordering on the decimal datatype
    doesn't always return an ordered data set then AFAICS the decimal
    datatype is broken."

    Later you said, (quote) "Yes/No - I now see your point...yes that is a
    bug."

    So, do you consider the YESNO data type to be broken?

    > I don't understand the relevance of my past
    > experience or indeed my future
    > requirements in deciding whether sorting
    > by the decimal datatype is broken.


    I inferred you do not use, nor would recommend readers to use, a data
    type that you consider broken. Would you clarify your position, please?

    > No, I would separate the baby from the bathwater and then dispose of the
    > bathwater.


    This is a bit vague but do I infer correctly you use the DECIMAL data
    type (the baby) but do not use the engine to sort DECIMAL data (the
    bathwater)? Or were you interpreting the idiom literally?

    Jamie.

    --
     

Share This Page