Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 23rd, 2004, 12:34 PM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default Export to text file without exponents

I have an Access database with a lot of information in decimals.

Somebody else needs to read this data but unfortunately, their import system can't seem to deal with exponents, hence I need to be able to export data to a text file without it going into exponent form.

Eg/

1.4276E-6

Should be written as

0.0000014276

is there any way of achieving this from Access?

Thanks

Andrew

Reply With Quote
  #2 (permalink)  
Old November 23rd, 2004, 12:51 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

I am assuming that you are using VBA to write the text file.

If so, when you write the number to the file, use the format function.

eg. Format(number, "0.####################")

The 0 in the format mask forces a 0 before the decimal point, and the #'s show only if a number is present. if number contained the number 1.4276E-6, then the output would be 0.0000014276.

You can add or subtract #'s depending on how many significant figures you would like to appear.

Mike

Mike
EchoVue.com
Reply With Quote
  #3 (permalink)  
Old November 23rd, 2004, 12:57 PM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Mike,

thanks for the info. Actually, I initially set up a macro to export to a text file, but that only gave 2 significant figures, so, as my system uses Excel to control all of the other apps anyway, I had a macro export to an Excel file which I opened automatically in Excel and then saved again in CSV format. Seemed to work fine, except now it seems my friends can't open a file with exponents which seems to be the default setting. Maybe exporting with macro's doesn't work for me and I'll have to use VBA?

Andrew

Reply With Quote
  #4 (permalink)  
Old November 23rd, 2004, 01:04 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

I am wondering if you were to change the format of the number in excel before you save it to a csv file, if it would carry over...

Mike
EchoVue.com
Reply With Quote
  #5 (permalink)  
Old November 23rd, 2004, 01:05 PM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Mike,

no, I tried that. On re-opening the file, it goes back to exponent. I think Excel doesn't save the formatting to Excel files.

Andrew

Reply With Quote
  #6 (permalink)  
Old November 23rd, 2004, 01:24 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

I just tried some things in Excel (2003) and I opened the csv file with notepad, and it looked like it carried it across. Although when I opened the file back in Excel it changed it back into Scientific notation. Check and see if the right format shows up in notepad, because this just may be a case where Excel is overthinking and giving you what it thinks you want (which you don't), while the actual file contains what you really want.

Mike
EchoVue.com
Reply With Quote
  #7 (permalink)  
Old November 23rd, 2004, 01:40 PM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Mike,

I tried that too, and on opening in notepad it is still in scientific notation.

I think VBA using your format function may be the way forward.

Andrwe

Reply With Quote
  #8 (permalink)  
Old November 23rd, 2004, 01:44 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Definitely not the easiest way out, but it'll work. Let me know if you need a hand with the code, I was just working on a similar project last week, and would be happy to post an edited version of it, if you would like.

Mike

Mike
EchoVue.com
Reply With Quote
  #9 (permalink)  
Old November 23rd, 2004, 02:24 PM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Mike,

whilst we have been talking, I've been putting some code together so I'll see if it works. Looks not too difficult...

Thanks for the offer, if I get stuck it could be useful!!

Andrew

Reply With Quote
  #10 (permalink)  
Old November 23rd, 2004, 03:27 PM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Mike,

using the Write and the Format function you mention above, seems to do the trick, thanks very much for your help. Strange that there doesn't seem to be a Microsoft format that does this. But maybe not that strange knowing Microsoft...

Thanks again,

Andrew
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
DTS text file export - Batch Process Tim Barbour SQL Server DTS 1 November 23rd, 2004 10:50 AM
Export CR report to TEXT using dotnet polin2358 Crystal Reports 0 October 8th, 2004 12:59 PM
Export a file Discoverer Report to a .pdf file alozano Oracle 0 May 6th, 2004 10:42 AM
Insert data in a text file and export them cutovoi Excel VBA 3 January 23rd, 2004 06:31 AM
Export Text problem Steven Access 1 July 7th, 2003 03:46 PM



All times are GMT -4. The time now is 09:58 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.