Lost decimal precision for single and double number format-Access2

I ran an update query to find certain values in a table and replace them with:

-9999.999999 for my single format columns (6 decimal places)
-9999.99999999999999 for my double format columns (14 decimal places)

Access presented a message saying it had done so but when I look at the 
table, the updated values are -10,000.

If I repeat the update query to replace -10,000 with the above values, 
Access presents a message saying it will replace the same number of rows as 
before with the above values, meaning that it really is -10,000 that it 
updated to the first time.

Why won't it give me the decimal precision it should?

Thanks,
Patrick


0
Utf
12/18/2007 6:27:06 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
879 Views

Similar Articles

[PageSpeed] 37

What is the field type?  Is it Number?  IF so, what is the field size 
(Integer, Long Integer, Single, Double, or Decimal)?

Single seems to be good to 3 decimal places After that it rounds based on 
the 4th decimal position
Double seems to be good to 11 decimal places after that it rounds based on 
the 12th decimal position

That is an observation based on a quick test, so it may not be totally 
accurate.
-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Pat" <Pat@discussions.microsoft.com> wrote in message 
news:438D8D52-A36A-4233-8281-7A08B29B70D3@microsoft.com...
>I ran an update query to find certain values in a table and replace them 
>with:
>
> -9999.999999 for my single format columns (6 decimal places)
> -9999.99999999999999 for my double format columns (14 decimal places)
>
> Access presented a message saying it had done so but when I look at the
> table, the updated values are -10,000.
>
> If I repeat the update query to replace -10,000 with the above values,
> Access presents a message saying it will replace the same number of rows 
> as
> before with the above values, meaning that it really is -10,000 that it
> updated to the first time.
>
> Why won't it give me the decimal precision it should?
>
> Thanks,
> Patrick
>
> 


0
John
12/18/2007 7:53:53 PM
On Tue, 18 Dec 2007 10:27:06 -0800, Pat <Pat@discussions.microsoft.com> wrote:

>I ran an update query to find certain values in a table and replace them with:
>
>-9999.999999 for my single format columns (6 decimal places)
>-9999.99999999999999 for my double format columns (14 decimal places)
>
>Access presented a message saying it had done so but when I look at the 
>table, the updated values are -10,000.
>
>If I repeat the update query to replace -10,000 with the above values, 
>Access presents a message saying it will replace the same number of rows as 
>before with the above values, meaning that it really is -10,000 that it 
>updated to the first time.
>
>Why won't it give me the decimal precision it should?
>
>Thanks,
>Patrick
>

A Single float number has 24 binary bits precision - approximately seven
decimal places IN ALL. That is, 3333333000000.00000000 is an approximation,
stored with a value somewhere between 3333332000000 and 333334000000. It's not
six or seven decimals *after the decimal point*. 

Similarly, a Double stores approximately fourteen significant digits - the
position of the decimal point is irrelevant, you only get those 14 places.

If you need more, use a Decimal number datatype.

             John W. Vinson [MVP]
0
John
12/19/2007 1:09:31 AM
Thanks John for clarifying.  That was exactly the problem.

Patrick

"John W. Vinson" wrote:

> On Tue, 18 Dec 2007 10:27:06 -0800, Pat <Pat@discussions.microsoft.com> wrote:
> 
> >I ran an update query to find certain values in a table and replace them with:
> >
> >-9999.999999 for my single format columns (6 decimal places)
> >-9999.99999999999999 for my double format columns (14 decimal places)
> >
> >Access presented a message saying it had done so but when I look at the 
> >table, the updated values are -10,000.
> >
> >If I repeat the update query to replace -10,000 with the above values, 
> >Access presents a message saying it will replace the same number of rows as 
> >before with the above values, meaning that it really is -10,000 that it 
> >updated to the first time.
> >
> >Why won't it give me the decimal precision it should?
> >
> >Thanks,
> >Patrick
> >
> 
> A Single float number has 24 binary bits precision - approximately seven
> decimal places IN ALL. That is, 3333333000000.00000000 is an approximation,
> stored with a value somewhere between 3333332000000 and 333334000000. It's not
> six or seven decimals *after the decimal point*. 
> 
> Similarly, a Double stores approximately fourteen significant digits - the
> position of the decimal point is irrelevant, you only get those 14 places.
> 
> If you need more, use a Decimal number datatype.
> 
>              John W. Vinson [MVP]
> 
0
Utf
12/19/2007 1:53:02 PM
Thanks John for the quick test, it's very helpful.  This illustrates what 
John Vinson said in the other response I received, that single number format 
holds 7 significant digits and double number format holds 15 significant 
digits (rather than places to the right of the decimal).

Patrick

"John Spencer" wrote:

> What is the field type?  Is it Number?  IF so, what is the field size 
> (Integer, Long Integer, Single, Double, or Decimal)?
> 
> Single seems to be good to 3 decimal places After that it rounds based on 
> the 4th decimal position
> Double seems to be good to 11 decimal places after that it rounds based on 
> the 12th decimal position
> 
> That is an observation based on a quick test, so it may not be totally 
> accurate.
> -- 
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Pat" <Pat@discussions.microsoft.com> wrote in message 
> news:438D8D52-A36A-4233-8281-7A08B29B70D3@microsoft.com...
> >I ran an update query to find certain values in a table and replace them 
> >with:
> >
> > -9999.999999 for my single format columns (6 decimal places)
> > -9999.99999999999999 for my double format columns (14 decimal places)
> >
> > Access presented a message saying it had done so but when I look at the
> > table, the updated values are -10,000.
> >
> > If I repeat the update query to replace -10,000 with the above values,
> > Access presents a message saying it will replace the same number of rows 
> > as
> > before with the above values, meaning that it really is -10,000 that it
> > updated to the first time.
> >
> > Why won't it give me the decimal precision it should?
> >
> > Thanks,
> > Patrick
> >
> > 
> 
> 
> 
0
Utf
12/19/2007 2:30:03 PM
Reply:

Similar Artilces:

Change default font format in Excel
I have an existing workbook with cell text entries in regular black font. I want to make a number of additional entries in various cells in this workbook, and I want all my text entries to be a different font format (bold, red). Is there a way to do this automatically without highlighting each entry I make and manually changing the cell format? Thanks. Michael, Copy the code below, right-click on your sheet tab, select "View Code" and paste the code in the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Cou...

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Payroll Batch Number
Is it possible to obtain the batch number of a transaction after it has been posted? I have been asked to create a report for our payroll department that lists the batch number in addition to the transaction detail that is contained in UPR30300. Thank you, Jeff Majchrzak No, the batch number is not available for reporting. -- Charles Allen, MVP "Jeff Majchrzak" wrote: > Is it possible to obtain the batch number of a transaction after it has been > posted? I have been asked to create a report for our payroll department that > lists the bat...

Money Upate SS Number
Al of a sudden when I try to update my portfolio ZoneAlarm warns that my computer is trying to send My social Number. If I answer No I don't get the updates. Is this a new Feature or what. I believe some Banks use your SS number as username. Nothing to do with Money - it's what your Bank demands for you to use on-line services. -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny UK Wishes/Suggestions mnyukwsh@microsoft.com "PapaFrank" <maglick69@hotmail.com> wrote in message news:KdOdnb00...

Pivot Table formatting #6
When I format my pivot tables I have "preserve formatting" checked under "PivotTable Options", and "Autoformat Table" unchecked. Even so, I either lose portions of my formatting, or different formatting is applied when I refresh. Does anyone know what I am doing incorrectly or have any other suggestions? Thanks, Phil Other things to try -- if they don't work, you could record a macro as you refresh and reformat the pivot table. Then, run that when you want to update. --Instead of selecting the cells to format the numbers, right-click the field but...

chart label reference based on the column number
In a worksheet with an embedded chart, I have a cell, S4, where I enter the number of the column I want to chart (these numbers are listed as labels in cells A2:R2). I can't figure out how to translate the number in S4 into the corresponding column letter. For example, if S4 contains "3", the chart title should be =$C$2. I think working with absolute references, R1C1, might be easier here, but somehow what I've tried, didn't work (the entire spreadsheet is based on relative addresses (A1). z.entropic in a cell (Z100) put =index(A2:G2,1,S4,1) Change G2 to whatev...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

lost menu bar #2
HELP!!! The main menu bar is missing off my excel sheet and I can't seem to find anyone to help me get it back. The menu I am missing is the one that includes - File, Edit, Insert and etc. I have tried...right clicking - customize - toolbars, but I don't seem to have the needed item. Anyone with suggestions - please reply... Thanks. Hi if you right-click on the menu bar you should see an item like 'General'. Check this item -- Regards Frank Kabel Frankfurt, Germany JoEllen OSBDC wrote: > HELP!!! The main menu bar is missing off my excel sheet > and I can't s...

Auto formatting features: How do I align page numbers in publicati
I've got a problem with my publication. I can't align even page numbers to the left without automatic moving the odd numbers to the left as well? Can someone help me? Cissy99 wrote: > I've got a problem with my publication. I can't align even page numbers to > the left without automatic moving the odd numbers to the left as well? Can > someone help me? You need to create a two-page master rather than a one-page master. What version of Publisher are you running? -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

how to turn off automatic format in Excel?
Hi Excel automatically change the first character in a cell to be uppercase. I just want lowercase. How can I turn off this function? Thanks Ngoc Hi Go to Tools / Autocorrect and uncheck Capitalize first letter of sentence. Andy. "ngoc" <linh@chello.no> wrote in message news:BOKNb.271$O41.819@amstwist00... > Hi > Excel automatically change the first character in a cell to be > uppercase. I just want lowercase. How can I turn off this function? > Thanks > Ngoc > ...

Formatting
I've always used MS Word as my email editor in Outlook. Is this possible in Live Mail. Nope. I'm afraid that's only possible using Outlook. In WLM you have to use the built in editor. Is there anything in particular that's lacking from the built in editor that you're looking for? Colin Brown WL MVP "jrchambe" <jrchambe@discussions.microsoft.com> wrote in message news:5EC21892-D39F-4219-AB0F-47BC14E1CD36@microsoft.com... > I've always used MS Word as my email editor in Outlook. Is this possible > in > Live Mail. "...

Formating
Hi, In a Column 'A' sales Commission is calculated and resulta is as 250, 200, 300, 330. I wanted to format a cell with a Blinking colours where value is 100 to 250, 250 to 300, 300 to 350 and 350 & above. please help to format my sheet ... thanks Ismail, Getting your cells to "blink" is not in the standard Excel formats What you want would require some extensive programming, certainly, ou of my capabilities. However, there are some great things you can do with Excel' CONDITIONAL FORMATTING. Let's say in column A you have numbers that are the result of formu...

Date Format turn to Year
Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =Year(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated. Thanks What's in A1? Are you sure it's a real date? "learning_codes@hotmail.com" wrote: > > Hi, > > I tried to convert the date to YEAR and then the year plus 25 Years > later. > > =Year(A1) I'm getting the result 1900 instead of 1965. > > I tried to add 25 years later to 1990 from 1965. > > Your ...

Problem access variable in On Format
Hi, I'm using Access via Office XP Pro. I am trying to format the Zip Code on the detail line of my report. I have tried the following lines of code in both the On Format event and the On Print event: If len([PostalCode]) > 5 Then : : end if or if len(Me.PostalCode) > 5 Then : : end if In both events and either code, I receive the error message: Access can't find the field 'PostalCode' referred to in your expression. If I put "PostalCode" as the source of the report's control...

Opening and formating a CSV file?
Could someone remind me how to handle CSV files please? I had assumed the Text Import Wizard would pop-up when I use File|Open, but the data goes straight into the worksheet. (Presumably because it is not 'delimited'?) Without the Wizard at my side, how do I get each comma-separated field in its own column? -- Terry, West Sussex, UK Try renaming your .csv file to .txt Terry Pinnell wrote: > > Could someone remind me how to handle CSV files please? I had assumed > the Text Import Wizard would pop-up when I use File|Open, but the data > goes straight into the worksheet...

How can I parse or Loop a list based on single field to Googlemaps
This is some code that will parse each sequential field [Point_#) into a list for googlemaps. [Form_Runs].WebRouteMap.navigate "http://maps.google.co.uk/maps?f=q&hl=en&q=" & "from: " & Me.Point_1 & (" to: " + Me.Point_2) & (" to: " + Me.Point_3) The table/form is laid outlike this: [Route_No] [Point_1] [Point_2] [Point_2] 5 North St, N1 East St, N3 High Road, W6 And the code will give this style of parsed list: from: North St, N1 to: East St, N3 to: High Road, W6 That works just ...

Formatting Cells in Excel 97
Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

single user mode
Hi, I need to be able to establish an exclusive connection to the database and do a restore to it. Nobody, can be connected to that database prior to the restore. Also, I need to be able to schedule it to run on a daily basis. I tried putting that db in a single user mode and then do the restored in a transaction but I got the error that the restore command is not allowed in the begin tran commit tran. How can I accomplish that? tolcis (nytollydba@gmail.com) writes: > I need to be able to establish an exclusive connection to the database > and do a restore to it. Nobody, c...

Numbering Recurring Items #2
Hey all, Is it possible to number recurring items such as tasks and appointments? I have several recurring appointments for lectures, and would like to show them as "Lecture 1" in week/occurence 01, "Lecture 2" in week/occurance 2, etc. Thanks, Murdoc -- Murdoc ## ------------------------ "The journey of 1000 miles begins with a broken fan belt and a leaky tyre." "Duct tape is like The Force. It has a light side and a dark side, and it holds the universe together." "If the facts don't fit the theory, change the facts." -Albert Einstein &...

fractions to decimal
I want to have one cell call a cell that has a fraction in it that is formated like so 0'-7/8" I need it to be formated like 0'-0 7/8" so with the following code I can convert it to decimal. [=SUBSTITUTE(LEFT(H69,FIND("-",H69)-1),"'","")*12+SUBSTITUTE(REPLACE(H69,1,FIND("-",H69),""),"""","")] the above code works when it is formated correctly Please help Thanks in advance Is this a custom cell format, or just content stored as the text default? if text, why not just search...

format a CD
Hi, how can I format a cd+rw re writeable disc aga ...

Excel devides every number by 100
Even when I open a brand new file. I input a number (ie 5) and get 0.05 back. I have tried doing everything in cell format etc.. anyone have an idea? it is very annoying and adding loads of work to my project. thanks "=?Utf-8?B?a2luZ3M=?=" <kings@discussions.microsoft.com> wrote in news:48A39EFC-2D09-420F-B2DF-6EF5C0DB6109@microsoft.com: > Even when I open a brand new file. I input a number (ie 5) > and get 0.05 back. I have tried doing everything in cell > format etc.. anyone have an idea? it is very annoying and > adding loads of work to my project. > ...

what is straight line depreciation and double line depreciation?
See http://en.wikipedia.org/wiki/Depreciation In article <928DC52C-D390-45C6-9847-693648A1A5DA@microsoft.com>, MISSY <MISSY@discussions.microsoft.com> wrote: "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message news:jemcgimpsey-F62312.22355624092007@msnews.microsoft.com... > See > > http://en.wikipedia.org/wiki/Depreciation > Quite a good article for wikipedia, but it misses one very important point. The original purpose of charging depreciation to the P&L was to build up a reserve in order to replace the asset when it became obsol...

page numbering
I want to change the way the date appears in the footer and header. Currently the date is day/month/year. I want month/day/year. setting the date to a particular format is easy in a cell. How do I set the date format in the header and footer? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ jamessem, you could use a macro like this to do it Sub test() With ActiveSheet.PageSetup .LeftFooter = Format(Date, "mmmm dd, yyyy") End With End Sub ...

Getting same number to appear over and again
Hi. I'm trying to change the E's I have entered in a column to 1's. However, in lieu of changing them one by one, I would like to do them all in one fell swoop. Any suggestions? John Hi John You could use find and replace. Select all of your data. Goto Edit>Find Find what:- E Replace with:- 1 HTH Martin John Do NOT multipost. See your answer in Excel group. And do a google search for newsgroup etiquette!! John Do NOT multipost. See your answer in Excel group. And do a google search for newsgroup etiquette!! John Do NOT multipost. See your answer in Excel ...