Rounding difference in Access 2003 text exports

We're finding rounding differences when exporting Access 2003 data to text 
files - about $4000 difference from the original Excel Worksheet.  # lines - 
about 250,000
Original Dollars - $800,000,000 +.  Access export is under by about $4000.  
Floating decimal in original Excel data, which is imported to Access and 
seems not to lose any data, more divisions and calculations are done driving 
the dollars out to a large customer base.  Before exporting, data is summed 
and foots to the original $800MM+.  Data is exported using Export Wizard as 
comma delimited.  I wonder - are there Advanced Options in the Export Wizard 
we should be using in order to get closer to the original numbers?
0
Utf
3/30/2010 4:52:09 PM
access 16762 articles. 3 followers. Follow

2 Replies
1449 Views

Similar Articles

[PageSpeed] 30

On Tue, 30 Mar 2010 09:52:09 -0700, Dana M <DanaM@discussions.microsoft.com>
wrote:

>We're finding rounding differences when exporting Access 2003 data to text 
>files - about $4000 difference from the original Excel Worksheet.  # lines - 
>about 250,000
>Original Dollars - $800,000,000 +.  Access export is under by about $4000.  
>Floating decimal in original Excel data, which is imported to Access and 
>seems not to lose any data, more divisions and calculations are done driving 
>the dollars out to a large customer base.  Before exporting, data is summed 
>and foots to the original $800MM+.  Data is exported using Export Wizard as 
>comma delimited.  I wonder - are there Advanced Options in the Export Wizard 
>we should be using in order to get closer to the original numbers?

If the field in Access is a Single Float, then it will be limited to about
seven digits of precision: e.g. 800,000,000 cannot be distinguished from
799,999,900 or from 800,000,099. 

If you use a Currency datatype instead of any sort of Number, you'll get a
range into the trillions and NO roundoff error.
-- 

             John W. Vinson [MVP]
0
John
3/30/2010 5:58:06 PM
"Dana M" <DanaM@discussions.microsoft.com> kirjoitti 
viestiss�:0540C38D-A3DC-47B6-886B-10DC06DE7116@microsoft.com...
> We're finding rounding differences when exporting Access 2003 data to text
> files - about $4000 difference from the original Excel Worksheet.  # 
> lines -
> about 250,000
> Original Dollars - $800,000,000 +.  Access export is under by about $4000.
> Floating decimal in original Excel data, which is imported to Access and
> seems not to lose any data, more divisions and calculations are done 
> driving
> the dollars out to a large customer base.  Before exporting, data is 
> summed
> and foots to the original $800MM+.  Data is exported using Export Wizard 
> as
> comma delimited.  I wonder - are there Advanced Options in the Export 
> Wizard
> we should be using in order to get closer to the original numbers? 


0
Risse
4/2/2010 6:41:47 AM
Reply:

Similar Artilces:

MS Excel 2003 cannot auto calculate formula, need to press F9 each time
hi, I don't know why my excel 2003 new worksheet cannot auto calulate formula (eg. summation), i need to press F9 and it will refresh and show the new figure. there is "calculate" word at the left hand bottom of the screen. what is the likely reason ? it was running fine 2 weeks ago. any advise is greatly appreciated. rgds. Tools>Options>Calculation tab, check Automatic -- Kind regards, Niek Otten Microsoft MVP - Excel <sg_s123@yahoo.com.sg> wrote in message news:d5393a73-eb7d-4e08-8fab-5f4ab895f77a@e23g2000prf.googlegroups.com... | hi, | | I don't know w...

Access 2000 Package and Deployment Wizard problem!
I have created an Access 2000 application that I now wish to distribute to users as an MDE file. The Access 2000 Package and Deployment Wizard does a fine job of storing the relevant installation files on a CD, exploiting A2K Runtime. However, I would prefer to let users download my application from a website. The Package and Deployment Wizard offers a 'Web Publishing' deployment option that appears to address this need. My only problem is that it doesn't work for me. I get the following error message, Unexpected error number 48 has occurred: File not found: WebPost.dll I...

SQL Back-end / Access Front-end using linked tables????
I have a backend that contains tables that I pull Driver information, Customer information , etc. (these are MAS 200 accounting software tables that are exported nightly to the SQL backend). I also have on the backend tables that I push information to; information that we enter on the forms located on the front end. I might not have this set- up correctly, but I'm linking all tables on the back end to the Access database on the front end. When trying to set up an Auto Lookup off of a query I've created in Access it says I need to go into the table change Data Type to the Lookup wizard,...

autocomplete (like Access)
Hi, I'm trying to restrict the entry of data into cells based on values in a list. What I need is similar to Data Validation but I want Excel to complete the entry after the user has entered just the first character. For example, In Microsoft Access, when creating a new table in Design view, first you enter the field name, then tab across to Data Type. Entering a "c" and then tab automatically enters "Currency" as the data type. Similar for "d", DateTime; "t", Text etc. So, in Excel, if I have a list, say, New York, London, Sydney, Tokyo. Typi...

Outlook 2003
I used to be able to do this in Outlook 2000. The string will be in either the subject or text body...or both. For example: looking for an email message about a specific invoice number 12345. Now, it does not find the number even when I know it's there....and can find it by hunting on my own. Thanks for any help you can offer~ "Hello_It''''s_Me-CA" <HelloItsMeCA@discussions.microsoft.com> wrote in message news:B942C394-5363-49BE-AFB3-04E34C1CE4BF@microsoft.com... >I used to be able to do this in Outlook 2000. The string will be in either > t...

Publisher 2003 Viewer is needed
Can Microsoft create a free viewer program for .pub files like they have for the other programs in Office? Can they? Yes Will they? Unlikely Mike Larry M. in Illinois wrote: > Can Microsoft create a free viewer program for .pub files like they have for > the other programs in Office? Larry M. in Illinois <Larry M. in Illinois@discussions.microsoft.com> was very recently heard to utter: > Can Microsoft create a free viewer program for .pub files like they > have for the other programs in Office? Could they? Yes Is it profitable for them to do so? Probably not. W...

Data from Access query to Excel
To pull data from an Access 2003 database, I have created the queries in Access, then import into Excel. The problem is that all the numbers that are pulled into Excel are text and need to convert them into numbers to run formulas on. I have converted a few sheets by hand, but, some have will over 50,000 rows. Is there a function to select all number colums (the colums are the same through out the sheets) and convert? Thanks There are instructions here for converting text to numbers: http://www.contextures.com/xlDataEntry03.html You can select all the columns, and only the num...

Outlook 2003 #224
When I print from Outlook I get the following screen: A dialog box opens and says An error has occurred in the script on this page Line: 1344 Char: 1 Error: Unspecified Error Code: 0 URL: res://C:\WINDOWS\System32\shdoclc.dll/preview.dlg All other applications work. Bob 5132361210 ...

Outlook 2003 hyperlinking error
Have just installed Outlook 2003, when I click on a html link within an email it opens my default web browser however the link appears in the browser with these additional symbols which stop the requested site from loading eg: http://%1%20http://www.microsoft.com Does anyone know how to fix this problem? Its driving me nuts. ...

Visio 2003: Custom Toolbars disappear
Sorry for the cross-posting, but I didn't get any responses in the visio.troubleshoot NG. I recently upgraded to Visio 2003 and have a perplexing problem. When I create a custom toolbar--or even when I customize a standard toolbar--they do not appear the next time I load Visio. Any ideas? I'm getting tired of recreating my custom toolbar every day! Thanks, Dave Go to View/Toolbars/ and you should see the list of customized toolbars you have made at the bottom of the menu. Selecting them will make them appear I tried to duplicate the problem you are having with seeing a standard ...

Summary of Difference between dates in years, months, days
I need to calculate the difference between 2 dates and then total them. Here's what I have so far: From To Length of Service 01/09/2003 31/01/2010 6y 4m 30d 01/06/2000 30/11/2002 2y 5m 29d Total of Service: ?????????? I've used the following formula to calculate the total days worked: =DATEDIF(A4,B4,"Y")&"y "&DATEDIF(A4,B4,"ym")&"m "&DATEDIF(A4,B4,"md&...

text box on exit
I have validate code on text box and want to cursor get back text box. I use Screen.ActiveControl.SetFocus, but the cusor goes to where my mouse click. Are there any way to let cursor stay in the text box until user enter valide data? Your help is great appreciated, On Wed, 12 Mar 2008 11:43:02 -0700, Souris wrote: > I have validate code on text box and want to cursor get back text box. > > I use Screen.ActiveControl.SetFocus, but the cusor goes to where my mouse > click. > > Are there any way to let cursor stay in the text box until user enter valide > data? &...

Custom Menus in Access 2007
I know in VB 6 you can create custom pop-up menu. Is there a way to do this in Access 2007? Active X? DLL? Hidden command that I can't find? Any tutorials or direction? Any help or direction that anyone can provide would be appreciated. In earlier versions, called "shortcut" menus. Right click on menu bar, select customise, show "shortcut menus" tollbar, select "custom". don't know if that tollbar has changed in 2007. (david) "Jerry Sheldon" <JerrySheldon@discussions.microsoft.com> wrote in message news:7431915F-33FF-4534-B5B4-C5E6F...

Access 2007 Runtime 10-30-07
1. Can I distribute Accees 2003 applications using Access 2007 runtime? 2. Do I also need the runtime extensions? 3. Will installing the Access 2007 runtime and extensions affect my current Access 2003 (full version) software, already installed? ...

Contact file not visible when importing or exporting contacts
I'm tidying up all my contacts files for email and also for my mobile ph using outlook. Have done this many times without any problems and am familiar with import export process. I exported all the contacts to an excel file to work on as it's much easier than through outlook. Had a hiccup at first as when I got to the select a contact file to export there was just a blank space. I exited outlook and went back in and it was all OK. Now I've finished the excel spreadhsheet and want to reimport it but it's the same problem in reverse. I can't "see" a co...

Works in 2003 but not 2007
When I reference the below function in 2003 I get function displayed... now in 2007 I get #NAME? Any idea what I need to change/set in 2007 so it will run this function? Function formulaText(x As Range) 'returns the text equivalent of formula in upper left cell in range x 'example: =formulatext(a1) returns the formula found in cell a1 of active worksheet 'example: =formulatext(a1:b5) returns the formula found in cell a1 of active worksheet formulaText = x.Cells(1, 1).Formula End Function Thanks, Mark Mark, It works fine for me. Are you stored in a stan...

Full Text Index PF
We are running 3 Exchange servers in 3 separate sites. Two of which (including our main mail relay) are running Exchange 2003 while the third is still on Ex2000. Our public folder store size is roughly 3.5 GB. I am considering setting up FTI on my public folder store. Our only desire is to speed up the search results on a public contacts folder that is replicated across all 3 servers. Is FTI our best option and how much should I expect it to speed things up? We are seeing about 15-20 second searches (and sometimes longer) currently. Not horrible, but the execs want it faster. Thanks, M...

Publisher 2003 files try to open in Publisher 97 #2
Back again, but with a different Pub 2003/Pub97 issue Have Office 2003 with Publisher 2003 and Publisher 97 both on my XP Pro system Recently I find that files created in Pub 2003 are being saved as files with the Pub 97 Logo, and when you try to open them from the file listing, they open Pub 97, which correctly posts a box telling me that it cant open the files. If I use Publisher 2003 to open the same files from within the application the work fine. When I select a 2003 Pub file which contains the Pub 97 icon, the file list does not contain Publisher 2003, but when I browse and locat...

Combine part of text from one col to another
I'm trying to combine only part of a string from one column with all th text of another column. For instance I want to create usernames to log into a network. If col has Bill and col2 has Smith I want to get just the B from col1 an combine it with col2 to make smithb in a new column. Thanks in advance -- Message posted from http://www.ExcelForum.com Try this: -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== =B1&LEFT(A1) "epac135 >" <...

Outlook 2003 Personal Address Book forwarding problem
This Outlook 2003 is connected to a Exchange 5.5 Server The problem is this, everytime I try to forward any e-mail and use any distribution list on the Personal Address Book it does not want to send the e-mail but if I try to send it any other way it works fine. Thanks ...

What program is trying to access e-mail address information stored in outlook?
I have one user (Vista Business, Outlook 2007 connected to Exchange 2003) who has today started to see a pop-up from Outlook saying: "A program is trying to access e-mail address information stored in Outlook. If this is unexpected, click Deny and verify your antivirus software is up-to-date." Anti-virus (McAfee) is up todate, and I'm doing a scan now and afterwards will do a separate scan for malware. This user tells me that he hasn't knowingly installed anything recently. My question is: is there any way to determine what the program is which is try...

HTML view with outlook and pocket pc 2003
When I download email from a pop3 server, the messages are displayed only in text format not in HTML format, i can't see the images in the email, I only see lines or dashes. Check with microsoft.public.pocketpc for help with PPC. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. After searching google.groups.com and finding no answer Mike Mikhail <smikhail@socal.rr.com> asked: | When I download email from a pop3 server, the messages | are d...

Can I use oulook from home and have access at work?
When I set up my outlook I lose all my inbox messages online and would not be able to read my email from work. Is there a way to have my inbox available on outlook and remain online? Just to clarify, are you asking how to set up Outlook at home to receive email from work? If that's your question, you need to ask your IT Admin or Exchange Admin if you can use OWA and how that's done. That wouldn't have email coming into your Outlook account but it is a form of Outlook, Outlook Web Access. This may or may not be allowed. Your IT staff would of course have all kinds of securi...

Outlook 2003 Reply Signature
When replying or forwarding an e-mail, the typed text follows the settings I have set in Tools> Options> Mail Format> Fonts, however the signature picks up the font from the senders e-mail. I have edited and saved the signature several times, but for some reason I cannot get it to provide a fixed font. The style is "Normal" which for my computer is set to Times New Roman, however, when responding, it converts to whatever the normal style was in the senders e-mail. This happens for all mail I respond to, even ones that I originated and were sent back to me....

compare two columns with different ranges in two worksheets
I need to compare two columns of data in two different worksheets and display a third one. Here it is an example: -(worksheet1!A1:A10), (worksheet1!B1:B10) and (whorksheet2!C1:C25) -this is my query, if C5 is already in (A1:A10) I want to display B5 in worksheet2!D5 I think it is tricky because you need to identity which row in the A1:A10 is equal to C5 to display B5 and the range are different. you could save my day chris90 In worksheet2!D1: =if(isna(vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)), "", vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)) HTH Kostis Vezerides brilliant, ma...