Double vs Decimal?

This is a spin off from the thread 
"Immediate Window: Annoying Notation?"

It's a *really* big deal to me all-of-a-sudden bc it sounds like
my current SOP might be at variance with good practice.

Lotta years ago (JET 3.something) and for reasons not recollected
I settled on Double as my data type of choice for big numbers
with lots of decimal places.

Now it's sounding like Decimal is the preferred vehicle, but I'm
a little confused - no... make that a *lot* confused.   If
Decimal is so "right", how come VBA doesn't support it directly
in the Dim statement?

Decimal seems tb a slam-dunk for tables - where it's an explicit
data type, but for Dim'd values there appear tb issues that I
should resolve before I go and shoot myself in the foot.

Sounds like Decimal can be loaded into a Variant - either with a
simple equate as in "myVariant = !WhatEverDecimal"  or by
coercion as in "myVariant = cDec(!WhatEverDecimal".


Is it a no-brainer to dim Variants instead of Doubles?
If so, what about the type checking that is sacrificed?


What about results where a computation operates on both Doubles
Decimals and the result rolls out into a Variant?  Guaranteed
Decimal result?    Does it matter?


Does anybody actually use all Variants for Dim'd fields where
decimals are necessary and there is no specific need for Double?

If so, do you always load the field by coercion - using cDec() -
or do you just do an equate?

Or do most people Dim Double for big numbers with lots of decimal
places?

-- 
PeteCresswell
0
PeteCresswell
1/26/2008 4:36:45 PM
access 16762 articles. 2 followers. Follow

7 Replies
1064 Views

Similar Articles

[PageSpeed] 57

On Sat, 26 Jan 2008 11:36:45 -0500, "(PeteCresswell)" <x@y.Invalid>
wrote:

I exclusively use Double, but in my line of business it doesn't occur
all that often. One problem with double is that you have to be careful
comparing two of them. They may be different only in the nth decimal,
so you write a function to compare two to a certain level of accuracy.
If I understand Decimal correctly, comparisons can be made directly.
I would be reluctant to use Decimals in a VBA program because of the
weak type checking with variants.

-Tom.


>This is a spin off from the thread 
>"Immediate Window: Annoying Notation?"
>
>It's a *really* big deal to me all-of-a-sudden bc it sounds like
>my current SOP might be at variance with good practice.
>
>Lotta years ago (JET 3.something) and for reasons not recollected
>I settled on Double as my data type of choice for big numbers
>with lots of decimal places.
>
>Now it's sounding like Decimal is the preferred vehicle, but I'm
>a little confused - no... make that a *lot* confused.   If
>Decimal is so "right", how come VBA doesn't support it directly
>in the Dim statement?
>
>Decimal seems tb a slam-dunk for tables - where it's an explicit
>data type, but for Dim'd values there appear tb issues that I
>should resolve before I go and shoot myself in the foot.
>
>Sounds like Decimal can be loaded into a Variant - either with a
>simple equate as in "myVariant = !WhatEverDecimal"  or by
>coercion as in "myVariant = cDec(!WhatEverDecimal".
>
>
>Is it a no-brainer to dim Variants instead of Doubles?
>If so, what about the type checking that is sacrificed?
>
>
>What about results where a computation operates on both Doubles
>Decimals and the result rolls out into a Variant?  Guaranteed
>Decimal result?    Does it matter?
>
>
>Does anybody actually use all Variants for Dim'd fields where
>decimals are necessary and there is no specific need for Double?
>
>If so, do you always load the field by coercion - using cDec() -
>or do you just do an equate?
>
>Or do most people Dim Double for big numbers with lots of decimal
>places?
0
Tom
1/26/2008 5:41:53 PM
Per Tom van Stiphout:
>They may be different only in the nth decimal,
>so you write a function to compare two to a certain level of accuracy.

Been there.... Can't recall how many decimal places out the
issues arose - but it was quite a few.... like more than 8.

Never occurred to me that it was the indefinite nature of Double.
-- 
PeteCresswell
0
PeteCresswell
1/26/2008 9:15:49 PM
Pete, surely this depends on which type you need.

My default is to use Double rather than Decimal. The math executes faster, 
and you don't need to worry too much about determining what scaling is being 
used when you write generic (re-usable) functions.

You do need to be aware of the floating point accuracy issues:
    http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems
That's actually quite simple.
For example, you don't loop like this:
    Dim dblLoop as Double
    For dblLoop = 0 to 1 step 1/7
    Next
because VBA will interpret 1/7 as a double, and 7 times may not equal 1 
exactly.

OTOH, Decimal has its problems too. VBA lacks a Decimal type, so you are 
using Variant, with the accompanying overhead (as well as the slower CPU 
execution.) JET does support Decimal, but is flawed:
    http://allenbrowne.com/bug-06.html
Similarly, if you try to force a field to Decimal by typing this into the 
Field row in query design, it fails:
    CDec(3)

So, my suggestions would be:
a) Use Double where appropriate, and learn how to handle floating point 
numbers.
b) Use Currency where you need a fixed point number with no more than 4 
decimal places.
c) Use Decimal for cases where you really need it, and learn where the bugs 
are to avoid.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"(PeteCresswell)" <x@y.Invalid> wrote in message
news:4d8np311iqm7lnfmm1kgnmmpni9ep1gu3f@4ax.com...
> Per Tom van Stiphout:
>>They may be different only in the nth decimal,
>>so you write a function to compare two to a certain level of accuracy.
>
> Been there.... Can't recall how many decimal places out the
> issues arose - but it was quite a few.... like more than 8.
>
> Never occurred to me that it was the indefinite nature of Double.
> -- 
> PeteCresswell 

0
Allen
1/27/2008 7:28:40 AM
Per Allen Browne:
>b) Use Currency where you need a fixed point number with no more than 4 
>decimal places.

I thought they retired "Currency" as a data type.

Can't cite my version of JET offhand, but the MS Access I'm using
is 2003 w/SP1.

The only numeric types in my "DataType" drop downs are:

	Byte
	Integer
	Long Integer
	Single
	Double
	Replication ID
	Decimal
-- 
PeteCresswell
0
PeteCresswell
1/27/2008 3:34:52 PM
Currency's its own data type. You're looking at the field size choices for 
the Number data type.

The Currency data type's still alive and well in Access 2007.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"(PeteCresswell)" <x@y.Invalid> wrote in message 
news:rs8pp39rd5krifovfi5poigce170q9s8mj@4ax.com...
> Per Allen Browne:
>>b) Use Currency where you need a fixed point number with no more than 4
>>decimal places.
>
> I thought they retired "Currency" as a data type.
>
> Can't cite my version of JET offhand, but the MS Access I'm using
> is 2003 w/SP1.
>
> The only numeric types in my "DataType" drop downs are:
>
> Byte
> Integer
> Long Integer
> Single
> Double
> Replication ID
> Decimal
> -- 
> PeteCresswell 


0
Douglas
1/27/2008 3:43:15 PM
On Jan 26, 4:36 pm, "(PeteCresswell)" <x...@y.Invalid> wrote:
> Decimal seems tb a slam-dunk for tables - where it's an explicit
> data type, but for Dim'd values there appear tb issues that I
> should resolve before I go and shoot myself in the foot.
>
> Sounds like Decimal can be loaded into a Variant - either with a
> simple equate as in "myVariant = !WhatEverDecimal"  or by
> coercion as in "myVariant = cDec(!WhatEverDecimal".
>
> Is it a no-brainer to dim Variants instead of Doubles?

Assuming WhatEverDecimal is an object (such as an ADODB.Field...) that
is strongly-typed as Decimal (...and !WhatEverDecimal.Type = adNumeric
is true) then typing the assignment variable as Variant rather than
Double is indeed a no-brainer.

? rs.Fields("WhatEverDecimal").Value
 1234567890.0987654321

? rs.Fields("WhatEverDecimal").Type = adNumeric
True

myVariant = rs.Fields("WhatEverDecimal").Value : ? myVariant,
TypeName(myVariant)
 1234567890.0987654321      Decimal

myVariant = CDbl(rs.Fields("WhatEverDecimal").Value) : ? myVariant,
TypeName(myVariant)
 1234567890.09877           Double

Jamie.

--

0
Jamie
1/28/2008 11:38:11 AM
On Jan 27, 7:28 am, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> my suggestions would be:
> a) Use Double where appropriate, and learn how to handle floating point
> numbers.
> b) Use Currency where you need a fixed point number with no more than 4
> decimal places.
> c) Use Decimal for cases where you really need it, and learn where the bugs
> are to avoid.

To the OP: this seems to be a sensible set of criteria to follow
(though in my experience I've only rarely encountered a scenario where
Double is most appropriate).

> OTOH, Decimal has its problems too. VBA lacks a Decimal type, so you are
> using Variant, with the accompanying overhead (as well as the slower CPU
> execution.) JET does support Decimal, but is flawed:
>    http://allenbrowne.com/bug-06.html

To Allen, I think you meant:

http://allenbrowne.com/bug-08.html

I may disagree with much of its contents (I strongly disagreed "Nulls
and zeros sort unpredictably" but that bug's now fixed, of course) but
I wouldn't want you to miss your opportunity to state your case :)

Jamie.

--

0
Jamie
1/28/2008 1:12:59 PM
Reply:

Similar Artilces:

Inventory: Average Lead Time vs Planning Lead Time
Hi, In "Cards > Inventory > Vendors" (ie. Item Vendors Maintenance) there is a field called "Average lead Time". I see that this is a calculated field and that makes sense. The Help for this field says: Average Lead Time - Enter the average number of days that pass between the time you place an order with this vendor and the time you receive the order. There is another field here called "Planning Lead Time". It's Help says: Planning Lead Time - Enter the number of days you use in planning between the time you place an order with this vendor and th...

double quotes
Hello, Because of limitations in my finance program, I'll periodically export the data to a text file, import the text file to Excel, where I manipulate the data as required. I then reverse the steps, saving the Excel file as text, then import the new text file back to my finance program. It all works Ok except for one thing - the amounts are stored in the program with a T in front, e.g., 100.00 would be T100.00. The problem is with larger amounts that involve a comma, such as 1,000.00 which would be T1,000.00. Excel insists on saving it with double quotes around it, "T1,000.00&q...

Why is VS 2005 so slow?
Hi, My company just migrates from VC++ 6.0 to VS 2005. Although there are many positive improvements, the developers immediately experience a negative difference - the new compiler does many things in a much slower speed (building projects, starting a debug session, exiting, ctrl-F12 GoToDeclaration, macro execution, etc). The slow speed is almost unbearable although someone may say it is due to the new complex features it provides. We are waiting for our x64 machines. Before then, is there anything we can do to improve the speed, e.g. disable the browse info generation (sacrifici...

Double email
Keep getting duplicate email? No where to find out how to change that, nothing in options or custom. Nothing in your post to help anyone try to help you. Version of Outlook? Type of account? How many? Did this always happen? When did it start? What did you change just before it started happening? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer Barbra <b.kostka@att.net> asked: | Keep getting duplicate email? No where to find out how to | change that, nothing in options or cu...

Suppressing zeros after the decimal point
How can I force zeros to display following a decimal point in Excel 2002? Whenever I type a number, such as 3.200, Excel drops the zeros and only enters 3.2 into the cell. Is there a setting that can be changed to fix this? (Using the "0.00" custom format seems to help, but if I have another number, such as 4.70, which only needs one zero to display, then I need another custom format for that one, and the next one, etc.) If anyone has a simpler suggestion, I'd appreciate it. Thanks! Hi AFAIK you can't achieve this with a custom format. Either you have three decimals or...

all the cells in excell put 2 decimal points in all numbers no ma.
i have to put 5 diget numbers in excel i format the cell to general and i get a number with 2 decimal points for me to enter 68555 i have to enter it as 68555.00 to get it to due away with the decimal point i reinstalled excel 2ooo but i can't seam to get it to go back to what it use to be. this problem justed popped up . never did this before Sounds like you need to turn off fixed decimals, if I understand your problem. Use Tools/Options/Edit and deselect Fixed Decimals. Bob Umlas Excel MVP "carroll616" <carroll616@discussions.microsoft.com> wrote in message news:E...

Creating a Line Chart with double Y Axis
Would be possible to create a Line Chart with double Y Axis on th Chart? The X axis is date and Y axis will be the value. Thannks -- Message posted from http://www.ExcelForum.com Select a series that you want on the secondary axis Choose Format>Selected Data Series On the Axis tab, select Secondary Click OK parker_yuan < wrote: > Would be possible to create a Line Chart with double Y Axis on the > Chart? The X axis is date and Y axis will be the value. Thannks. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Visio Hyperlinks
Hi - currently using Visio and would like to save the workflow as a template for others to 'save as' and execute - need to know if the hyperlinks will work within the template and also whether or the current hyperlinks require a 'right click' to execute???? Would like to execute using double click feature? Any advice would be GREAT!! Thanks..../d If you have a Hyperlink on a Shape, then change the formula in the EventDblClick cell in the spreadsheet to =HYPERLINK(Hyperlink.Row_1.Address) "Deb" <Deb@discussions.microsoft.com> wrote in message news:2E...

WLM sending duplicate (double) emails
I have an annoying issue with WLM version 14.0.8089.0726 running on W7. Sometimes I send a mail from my MSN.com account and it sits in my Outbox for hours until I close WLM and then reopen it. This results in the mails in the Outbox being sent twice; in the "Account" column for one message it says my MSN mailbox name, and on the duplicate message the "Account" column is blank. Is anyone else having this issue? In Tools|Options on the Send tab, is the "Send messages immediately" box checked? -- Patrick P. www.boydcomputersvcs.com "...

Double the Attachments
Any idea why some of our emails have two copies of each attachment? At first I thought it was a sender fluke, but have recently noticed that some of our own sent e-mail messages, but not all, end up in the 'Sent Mail' folder with twice the attachments they had when originally sent. Do we have an errant setting that could be causing this anomaly? ...

Screen color vs printed color
Using Pub 2003, with an HP Deskjet 1120C printer. (yes it's old) Replaced color cartridge with a "generic" one.( yes I'm cheap) Pub and Word cannot print RED but give me some kind of maroon. I printed a PUB color scheme sheet and there are NO reds on it. Since I have no other programs to print from I am wondering if Word and Pub have problems with the color RED? -- McM I read a few forum messages on the HP site about your printer. What version Windows are you using? You might find some of the messages enlightening. You could try a different driver, there are suggestions. ...

refactoring (base vs child)
Hello, I have a follwoing problem: I got a class with members and I need to add another 2 classes which will be childeren of this class but memebers from base class will now be part of one of the child class. How can I modify my code ,I mean where I refer to base which doesn't have those members anymore (because they are in child). What is the best way to refactor this? Thanks! *** Sent via Developersdex http://www.developersdex.com *** csharpula csharp wrote: > Hello, > > I have a follwoing problem: > > I got a class with members and I need to add ...

WORD vs. EXCEL: How to Optimize
Hi all... I have a fairly complex spreadsheet that also incorporates lots of legal text. The app allows reps to enter info about prospects, and it computes pricing, generates legal documents, and other info. Clearly, EXCEL handles the math, but is weak with text. Depending upon certain inputs, different blocks of text may be used. Ideal world: utilize the best of both. Question: should I make this a WORD_centric document or an EXCEL-centric document, and how to set up the links between the two. Many thanks... /Randy Randy, It depends in part on the layout you want. With Excel, you ge...

Relationships in frontend vs. backend
I recently split our Access database into front-end and backend. This week I opened the backend, created a number of tables, and established their relationships to each other and to existing tables. It was very disturbing to open Relationships in the front-end and discover the old relationship model. Then I opened the backend and found it as I had left it. Where is the correct place to edit relationships? How to I keep the two in sync? Thanks for your help. The relationships and integrity is always maintained in the back end. if you think about this, what would happen if we had ...

Can I replace the attachment file when double chick attachment?
I want to replace the attachment when double chick the attachment. I use outlook form to do it. Beause attachment read in outlook is not a cancelable event, I can't do it directly. I found outlook always store attachments file in directory: "%USERPROFILE%\Local Settings\Temporary Internet Files\OLK*". So if I want to replace the attachment when user double chick the attachment, I can repalce the attachment file(will display) in that directory. I have some test, outlook allow me do that. But I meet two question: First: the directory is different with each user, I always start wi...

Owner-draw checkbox and Double-click
Hello, I have an owner drawn checkbox (see my post "Checkboxes: SetCheck() and BS_OWNERDRAW", 8/18/2003). It works great whether the user clicks on it or hits the space bar when it has the focus. However, when the user double-clicks the checkbox and tabs to another control, a heavy black rectangle is left behind. I noticed that this related to my usage of DrawFocusRect() in my DrawItem() function, but I cannot figure out how to properly address the problem. DrawFocusRect is called in two different places in DrawItem(): if( lpDrawItemStruct->itemAction == ODA_DRAWENTIRE ) { ...

Emails Doubling
People are receiving doubles of my emails. Why is this? Sometimes I receive doubles of others' emails also. "pammor" <pammor@discussions.microsoft.com> wrote in message news:CE3A796F-FC12-4712-9510-8163FB406E73@microsoft.com... > People are receiving doubles of my emails. Why is this? Sometimes I > receive > doubles of others' emails also. Go to the Tools menu>Accounts> ensure that you do not have your account set up twice, if so remove one instance of it. -- Regards Steve. MS-MVP. MAIL. [DTS] UK. http://www.getsafeonline.org...

Outlook vs outlook express identities
Will Outlook handle email; similar to outlook express i.e. logon/logoff to each individual user so each individual will only see their e-mails? To accomplish this in Outlook, you could simply create seperate email accounts for each different user: Using Outlook on a computer you share with other people http://office.microsoft.com/en-us/assistance/HA011110031033.aspx?Product=out Good luck! "reitwayd" <reitwayd@discussions.microsoft.com> wrote in message news:BDE02F55-A8BF-47C9-A7CE-4153D5EE07A9@microsoft.com... > Will Outlook handle email; similar to outlook express i....

Sharing between XP & Win7: workgroups vs. homegroups?
Okay basic question here, are what were known as "workgroups" in XP and earlier, now known as "homegroups" in Vista and later? Why do you need to set passwords when creating a homegroup, when there was none needed with workgroups? If you want to connect to an XP machine with workgroups, do you need to disable or enable homegroups? Will a password-enabled homegroup work with a non-passworded workgroup? Yousuf Khan Yousuf Khan <bbbl67@spammenot.yahoo.com> wrote in news:4b80766f$1@news.bnb- lp.com: > Okay basic question here, are what were known a...

double-click in between two rows or two columns
How to avoid cursor jumping around to different positions when I double-click in between two rows or two column ? Thanks Hi This is a feature of Excel! When you double click the border of the selected cell, the cursor will go to the edge of the contiguous data on the sheet. It's very useful for jumping to the start/end of data. I don't know of a way to disable it. -- Andy. "Ben" <anonymous@discussions.microsoft.com> wrote in message news:bf5d01c4382c$a19b1bf0$a601280a@phx.gbl... > How to avoid cursor jumping around to different positions > when I double-c...

MS Money 2000 vs 2004 compatibility
I am currently using MS Money 2000. If I install the new version, MS Money 2004, will the new software be compatible with my old Money 2000 file formats? For example, can I run reports in Money 2004 using Money 2000 records and files? Thanks. Bill Bender I would download the Money 2004 demo and try to import your database from Money 200 before you buy. You should be able to use your money2000 file, but I had problems upgrading from 2000 to 2003. Basically, money2000 has the ability to corrupt its own files. It can still read them but a newer version of money cannot import it. I had Mic...

offline backup vs online
Hi all im trying to figure out what the difference between these types of backups. Is an offline backup mean that the database is on tape? and an online backup would mean that the databse is on disk? Thanks An online backup is a backup that occurs databases are mounted (accessible). An offline backup occurs while the databases are unmounted. Teo Gomez "Skipster" wrote: > Hi all im trying to figure out what the difference between these types of > backups. Is an offline backup mean that the database is on tape? and an > online backup would mean that the databse is...

Programmatic access to VS IDE tooltip info
Hi, I am looking for a straightforward (or pre-implemented) way to access the XML documentation included with the framework's assemblies (just as the VS IDE does when it pops up tooltips in the code window). Anybody know the best way to proceed? The IDE already parses the files and then I assume it uses reflection to associate a member signature with the corresponding xml entry, however I do not wish to repeat this process if possible or at least use some sample code as a starting point. Any help is greatly appreciated. Many thanks! Chris see subject ...

PM Apply zoom window vs Discount taken
HI, When I drill down payables transaction up to the Payable Apply Zoom window, I can see that my invoice amount is 9078.45, but only 6936.33 is applied. However, the amount remaining is 0. Looking at the tables, I found that the difference came from the discount taken amount of 2142.12 (ActualDiscTakenAmount field). It is very confusing, we were looking for an apply error and the customer was loosing confidence in the system. Would it be possible to modify this screen to show the discount amount in order to be sure everything is OK. I know I can do it with modifier but it would be...

Exchange Archiving Solutions
I'm interested in hearing from anyone who has experience with either KVS or EAS for Exchange archiving. We're evaluating both at the present time, and they're pretty comparable for our environment with a slight edge to KVS. Particularly interested in PST migration, seamless integration with Outlook for end users and quick/accurate retrieval of archived messages based on specific criteria when needed for records requests. Thanks, Jeff On Thu, 18 Nov 2004 13:34:11 GMT, jeff.nospam@zina.com (Jeff Cochran) wrote: >I'm interested in hearing from anyone who has experience wi...