Stop Rounding in the Table

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

 

[tina]

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

 

[Jamie Collins]

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.

--

 

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

 

[Jamie Collins]

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.

--

 

[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 2003Note 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")

 

[Jamie Collins]

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.

--

 

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

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!!
smoke screen. <<
... and ..impression you read Allen Browne's biased attempt to discredit the
DECIMAL type <<
... and ..
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!"

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

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.

 

[Jamie Collins]

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

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.

--

 

[Terry Kreft]

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

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

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.

 

[Pieter Wijnen]

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

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

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

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.

 

[Pieter Wijnen]

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

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

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

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.

--

 

[Jamie Collins]

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.

--

 

[Jamie Collins]

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.

--

 

[Pieter Wijnen]

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

Pieter

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.

--

 

[Terry Kreft]

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

I'll answer in-line.

--

Terry Kreft

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

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

I did presumed that a .co.uk domain would recognise playful sarcasm

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

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

This is a bug in Access.
It occurs when you group by a yes/no field that contains nulls.
You can work around it by choosing First instead of Group By in the
Total
row under the ynLessonCharge field.

Details:
Unfortunately, Microsoft designed the yes/no field wrongly, so it
cannot
handle the Null value. Then they assumed that the query engine would
never
have to handle nulls in a yes/no field. But there are many cases where
there
are nulls in y/n fields. The classic example is a query like yours with
an
outer join.

There are various errors associated with this core issue. The "No
current
record" error results from JET being unable to GROUP BY the Yes/No
field
that contains Nulls. Other queries actually crash Access (shut down by
Windows) when JET can't handle the nulls.
[end quote]

For the record:
· I have not tested or reproduced this bug;
· I have not encountered this bug because I do not use the YESNO data
type in the 'real world';
· I advise people to avoid the YESNO data type based on more
fundamental issues (three valued logic and portability).

I would not normally refer or allude to this bug other than when
employing irony or other troll-like tactics.

Having questioned my honesty, do you have any further comment on this?

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

Good. Have you ever had need for a fixed point that can support more
than five decimal places and/or to avoid banker's rounding, where data
containing negative values were required to be sorted by the engine
into descending order? I guess my question in reply is, have you ever
analysed a customer's requirements and ruled out the DECIMAL data type
based on this bug? Or have you ruled in out for other reasons?

Jamie.

--

 

[Jamie Collins]

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.

--

 

[Terry Kreft]

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

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

I did presumed that a .co.uk domain would recognise playful sarcasm

order,

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

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

This is a bug in Access.
It occurs when you group by a yes/no field that contains nulls.
You can work around it by choosing First instead of Group By in the
Total
row under the ynLessonCharge field.

Details:
Unfortunately, Microsoft designed the yes/no field wrongly, so it
cannot
handle the Null value. Then they assumed that the query engine would
never
have to handle nulls in a yes/no field. But there are many cases where
there
are nulls in y/n fields. The classic example is a query like yours with
an
outer join.

There are various errors associated with this core issue. The "No
current
record" error results from JET being unable to GROUP BY the Yes/No
field
that contains Nulls. Other queries actually crash Access (shut down by
Windows) when JET can't handle the nulls.
[end quote]

For the record:
· I have not tested or reproduced this bug;
· I have not encountered this bug because I do not use the YESNO data
type in the 'real world';
· I advise people to avoid the YESNO data type based on more
fundamental issues (three valued logic and portability).

I would not normally refer or allude to this bug other than when
employing irony or other troll-like tactics.

Having questioned my honesty, do you have any further comment on this?

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

Good. Have you ever had need for a fixed point that can support more
than five decimal places and/or to avoid banker's rounding, where data
containing negative values were required to be sorted by the engine
into descending order? I guess my question in reply is, have you ever
analysed a customer's requirements and ruled out the DECIMAL data type
based on this bug? Or have you ruled in out for other reasons?

Jamie.

--

 

[Jamie Collins]

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.

--