Immediate Window: Annoying Notation?
(PeteCresswell)
-----------------------
?YieldRate
5.34193893129773E-02
-----------------------
Which means nothing to me, so I wind up having to wrap
it in a Format():
--------------------------------
?format$(YieldRate, "0.0000000")
0.0534194
--------------------------------
Not exactly the end of the world, but a nuisance factor -
especially when somebody is looking over my shoulder and I'm
demonstrating something to them. We lose the flow.
Anybody know of some parm, option, or setting that will cause MS
Access to drop the "E" notation?
--
PeteCresswell
Tom van Stiphout
wrote:
Even if you're not a scientist it may be wise to spend 5 minutes
learning about scientific notation, so you can read the value. This
notation is popular because most numbers, whether big or small can be
expressed with a limited number of digits.
It essentially says:
5.34193893129773 * 10^-2
IOW: move the decimal point two steps to the left (creating a smaller
number):
0.0534193893129773
Similarly 5.34193893129773E+05
would say: move the decimal point to the right (bigger number):
534193.893129773
Indeed the format function is one of the few ways to express this in a
pattern you are more accustomed to.
-Tom.
Jamie Collins
You are probably using data of type Double (FLOAT), whereas you would
seem to want Decimal (DECIMAL) e.g.
? 54321 / 987543
5.50062123877137E-02
? CDec(54321) / 987543
0.0550062123877137501860678472
Jamie.
--
Jamie Collins
In case it wasn't clear: division results in Double unless one of the
values is Decimal:
? TypeName(54321 / 987543)
Double
? TypeName(CDec(54321) / 987543)
Decimal
SELECT TYPENAME(54321 / 987543), TYPENAME((54321 + 0.1 - 0.1) /
987543)
returns 'Double' and 'Decimal' respectively.
Jamie.
--
Tom van Stiphout
<jamiec...@xsmail.com> wrote:
However there really is no decent support for the Decimal data type in
VBA. Personally I'm not going there. Fine for quick conversions in the
immediate window, but not for use in my app.
-Tom.
Jamie Collins
> there really is no decent support for the Decimal data type in
> VBA.
Admittedly Decimal does have a "version 1.0" feel about it (the .NET
framework got "version 2.0", of course). Not being able to strongly-
type variables and return values (Function and Property Get) is a
particular annoyance but the same can be said for the handling of NULL
in VBA e.g. if you want to test IsMissing you have to use Variant i.e.
loss of strong typing; ADO Parameter objects are a fair workaround.
However, *support* per se for Decimal is near universal in VBA e.g.
most built-in functions/expressions handle the Decimal type without
coercing the result to Double.
> Personally I'm not going there.
Better get to like scientific notation and loss of precision then ;-)
In all seriousness, it's hard to avoid round here because Jet, unlike
VBA, uses the Decimal type natively e.g.
? TypeName(0.5)
Double
? CurrentProject.Connection.Execute("SELECT TYPENAME(0.5);")(0)
Decimal
? TypeName(1234567890123456789)
Double
? CurrentProject.Connection.Execute("SELECT
TYPENAME(1234567890123456789);")(0)
Decimal
Jamie.
--
(PeteCresswell)
>In all seriousness, it's hard to avoid round here because Jet, unlike
>VBA, uses the Decimal type natively e.g.
Is that to suggest that if I go into my back end and change each
and every Double field to Decimal that it sb transparent other
than the added precision and more readable Immediate window
presentation?
Seems like I went there a number of years ago and there was some
sort of Gotcha....
--
PeteCresswell
Tom van Stiphout
wrote:
From the help file:
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.
If it ain't broke, don't fix it.
-Tom.
Jamie Collins
Jamie Collins
>
> Is that to suggest that if I go into my back end and change each
Not at all; on the contrary, if you choose to use Double you may need
to go into your back end and explicitly cast implicit Decimal values.
> ...other
> than the added precision and more readable Immediate window
> presentation?
I guess you are asking, "Other than the added precision and more
readable Immediate window presentation, why explicitly use Decimal?"
Well, the answer may be found in basic data modelling: what else can
you use when you need more than four decimal places and/or more than
15 significant figures?
In the product I currently support we have an numeric attribute that
needs to be accurate (I stress the word "accurate") to a decimal scale
of five decimal places and 16 significant figures of overall
precision, another money attribute requires a decimal scale of nine
and overall precision of 19. Double simply does not have the accuracy
(it is classified as an "approximate" data type) and floating point
semantics are not acceptable. Jet's and VBA's Currency, a fixed point
type type, has a fixed precision of four, which is not enough (and
precision of 19, which is often too much <g>). When you contemplate
the alternatives, such as scaling your own data type using a column(s)
of another type, *that* is when you appreciate that Decimal does enjoy
a good level of support in VBA and Jet.
> Seems like I went there a number of years ago and there was some
> sort of Gotcha....
Well, there was a sort Gotcha <vbg>. Known as the Decimal sort bug
where, for descending order only, the correctly-sorted set of negative
values appeared in the wrong position in the resultset i.e. at the
high end above the correctly-sorted set of positive values rather than
between the set of zeros and the set of NULLs at the low end. It was
incorrectly reported at the time that the values were more 'random'
but in fact the results were entire predictable.
It takes many words to explain the bug because it was quite subtle!
So, if descending order was required and sorting in the engine was the
only option, you had to remember to instead of
ORDER BY fldNumber DESC;
you had to do something like
ORDER BY fldNumber IS NOT NULL, fldNumber >=0, fldNumber DESC;
I'm using the past tense because the bug has now been fixed :)
Jamie.
--
Jamie Collins
> From the help file:
> 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.
Note "At this time." I think it is a fair assumption that if VBA7 had
become a reality then Decimal would have become a native type,
probably a reference type as is String in VBA6 (remember floating
point types and integers enjoy *hardware* support, which is not the
case (yet) for fixed types; I *think* Decimals in VBA6 are scaled
using four Long Integer plus a pointer).
> If it ain't broke, don't fix it.
Was that irony? Because CDEC() expression has been broke in Jet since
Jet 4.0 and they ain't fixed it:
ACC2000 CDec Wrong Number of Arguments Error in a Query
http://support.microsoft.com/kb/225931/en-us
As I mentioned up-thread, this isn't so much of a problem because,
unlike VBA, native decimals are considered to be of type DECIMAL.
Arithmetic involving a value of type DECIMAL coerces the result to
DECIMAL (the same is true of VBA, in this case).
Jamie.
--
(PeteCresswell)
>I guess you are asking, "Other than the added precision and more
>readable Immediate window presentation, why explicitly use Decimal?"
Actually, my real question/agenda is: Should I go through my app
and change everything that's Double to Decimal?
It's a financial app like the one you describe.... Dollar
balances in the billions - yet needing a lot of decimal places.
It's starting to sound to me like:
----------------------------------------------------------------
1) I need to go through the tables and, wherever there's a
Double either change it to Decimal or come up with an explicit
reason why it should remain Double.
2) Go through all my code and change anything that's Dim'd as
Double to Variant.
----------------------------------------------------------------
Am I on the right track?
I don't know how/when I came up with Double as my SOP field for
big cash numbers. It was probably 10-15 years ago when I was
starting out and Double was the field I came up with that would
hold big numbers with lots of decimal places. One of those
things where you make a decision, go with it, don't have any
problems, and never re-think it - having moved on to other
things.
--
PeteCresswell
Jamie Collins
> my real question/agenda is: Should I go through my app
> and change everything that's Double to Decimal?
>
> It's a financial app like the one you describe.... Dollar
> balances in the billions - yet needing a lot of decimal places.
>
> It's starting to sound to me like:
> ----------------------------------------------------------------
> 1) I need to go through the tables and, wherever there's a
> Double either change it to Decimal or come up with an explicit
> reason why it should remain Double.
>
> 2) Go through all my code and change anything that's Dim'd as
> Double to Variant.
> ----------------------------------------------------------------
>
> Am I on the right track?
>
> I don't know how/when I came up with Double as my SOP field for
> big cash numbers. It was probably 10-15 years ago when I was
> starting out and Double was the field I came up with that would
> hold big numbers with lots of decimal places. One of those
> things where you make a decision, go with it, don't have any
> problems, and never re-think it - having moved on to other
> things.
You've touched on many issues here.
I don't think you should reengineer your code in the way you suggest.
That would be committing the same mistake as those who add an
autonumber primary key to every table i.e. done out of habit, knee
jerk reaction rather than engaging the brain. Fix bugs instead ;-)
Think back to when MS were promoting ADO over DAO ("In previous
versions of Access, Data Access Objects (DAO) was the primary data
access method. That has now changed. Although DAO is still supported,
the new way to access data is with ADO..."
http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx).
Even in that climate, MS were not suggesting that DAO code should be
reengineered using ADO.
I don't create SQL schemas for myself. I work on behalf of other
people which means I have domain experts to consult and the internet
for background research. Because writing specs is what these guys do
all day, they tend to be meticulous. Much implementation is deferred
to the coder (or their technical lead) but most of the *data* issues
are resolved during spec reviews. So it rarely the case that I don't
know in advance things like largest positive/negative value to
support, precision and decimal scale, default value, rounding
algorithm, domain checking and validation rules, etc.
Due to the nature of the business I've been involved with (corporate
entity modelling, financial reporting, workflow management, pensions,
hospital prescribing) I have encountered only one attribute a where a
designer explicitly asked for a floating point value (and I still
wonder whether it was the right choice).
So I've always had to work with very precise values and, frankly, I'd
be surprised if I were the only one. It seems to me that genuinely
floating point data seems to be far more frequent in the natural
sciences than in business environments. Hence, I'm biased against
floating point data types because of their approximate nature.
It may well be the case that at the higher end of the date range you
mention that Double was the best data type for your purposes. I've
only been exposed to Jet since version 3.51 which had CURRENCY but I
am most familiar with Jet 4.0 which introduced the DECIMAL type. Both
are fixed point types. The main differences are that CURRENCY exhibits
banker's rounding by nature whereas DECIMAL exhibits rounding by
symmetric truncation (a.k.a. no rounding) by nature, and that
CURRENCY's precision (19) and decimal scale (4) and fixed, though of
course reduced values are possible using validation rules e.g.
CREATE TABLE Test (
data_col CURRENCY DEFAULT 0.00 NOT NULL,
CONSTRAINT data_col__precision_5 CHECK (data_col BETWEEN -999.99 AND
999.99),
CONSTRAINT data_col__decimal_scale_2 CHECK (data_col = FIX(data_col *
100) * 0.01)
);
I think what you should do is review the list of new features (as well
as the bug fixes and existing features whose behaviour have been
altered) so see if you can take advantage of them in future
development. So, as regards Decimal, you really should have done this
literally years ago (pity anyone who decided, I'll defer for this
release of Jet..." <g>) For example, user level security and
replication have been removed from Jet with effect from ACE and even
if you don't plan to port to Access 2007 format I think the direction
the Access team is taking with the engine should inform your current
development plans.
In conclusion: reengineer it? probably not. Re-think it? definitely
yes! :)
Jamie.
--
(PeteCresswell)
>I don't think you should reengineer your code in the way you suggest.
>That would be committing the same mistake as those who add an
>autonumber primary key to every table i.e. done out of habit, knee
Seems like the gist of what's been said so far, though, is that
Double is a data type that should only be Dim'd when there is an
explicit reason to do so.
For values with decimal places, doesn't that leave Variant as the
only remaining choice?
And if that's true, isn't the developer giving up some of VBA's
built-in type checking? i.e. Aren't we back to a non-typed
language where we have to explicitly ensure that the values we
feed to computations are numeric?
--
PeteCresswell
Jamie Collins
> Seems like the gist of what's been said so far, though, is that
> Double is a data type that should only be Dim'd when there is an
> explicit reason to do so.
Perhaps you are asking the wrong person because I think you should
always have an explicit reason regardless of the type you are
Dim'ing :)
> For values with decimal places, doesn't that leave Variant as the
> only remaining choice?
Don't forget Currency: there are situations where Banker's rounding is
appropriate and its scale and precision are large enough.
> And if that's true, isn't the developer giving up some of VBA's
> built-in type checking?
I would emphasize the word "some"...
> i.e. Aren't we back to a non-typed
> language where we have to explicitly ensure that the values we
> feed to computations are numeric?
Even when using VBA native types range checking is more often than not
required.
If you've been using VBA for 15 years without using Decimal I would be
surprised if you aren't already using Variant in situations where
you'd ideally be using strongly-typed values. The most common scenario
in my experience is arguments in sub procedures, where you need to
type the value as Variant either to allow the NULL value or to be able
to test the parameter value using IsMissing(); I would use a strongly-
typed local variable to test whether the supplied (Not IsMissing)
parameter value is of the correct type.
Temporal values are a particular pain. VBA has but one temporal type
Date, which is based on Double; note that Standard SQL supports
distinct types for DATE, TIME and TIMESTAMP (DATETIME in Jet)
respectively. VBA's temporal functionality supports one second as the
smallest time granule but the floating point nature means that
considered sub-second values may be passed to my procedure, meaning I
have to put in code to handle: round them, reject them, revert to a
default value, etc. Speaking of default, zero corresponds to an actual
Date value, meaning that I either haver do date range checking (i.e.
conclude that anything out of reasonable 'recent' range is considered
'null') or we're back to Variant and IsMissing.
[Note that I have a strong aversion to using Variant to the point
where I avoid it whenever possible, even if that means creating custom
classes and interfaces (the Implements keyword in VBA and all that
jazz).]
A similar situation exists for VBA's fixed width text type, String * N
e.g. I can do this:
Dim ISBN10 As String * 10
but I can't do this:
Public Function BookRemove(ByVal ISBN10 As String * 10) As Boolean
In the latter case the best I could do would be to type the argument
as variable width text (String) then test that the value passed was of
exactly ten characters... actually, that's not strictly correct
because I'd probably do something like this:
Public Function BookRemove(ByVal ISBN10 As String) As Boolean
Dim localISBN10 As String * 10
On Error Resume Next
localISBN10 = ISBN10
On Error GoTo 0
But it would still not be 'job done' because even a variable strongly
typed as String * 10 may have been right-padded with space characters,
so the next step would probably be something like this:
If Not UCase$(localISBN10) Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9]
[0-9][0-9][0-9X]" Then
Err.Raise vbObjectError + ERR_NO_ISBN10_PATTERN, TypeName(Me),
ERR_DESCRIPTION_ISBN10_PATTERN
Exit Function
End If
In conclusion, strongly-typed variables in VBA are indeed a fine thing
but they only take you so far. I'd say there are situation where you
can use Variant to some advantage but I am definitely not trying to
encourage anyone to use Variant in every case.
Jamie.
--