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

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

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

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

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

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

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

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

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

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