Export spec. produced fields with incorrect field lenghts

[reiman]

Using MS Access 2000 I am trying to export a delimeted file with 6 decimal places (all numbers less than 1) in one of the fields. The export spec only provides one level of formatting for me (double). I have coded the field in the table allow up to 6 decimal places but no matter how I try to export the table to a text file I get either rounding (with a number format) or unexpected mixed numbers (whole and fraction, using a text format). The files are too big to export into excel (over 56,000 lines) and there are too many files to do this extra step for formatting even if they weren't. Can anyone tell me how to export out a number in a text file with the correct decimal places?

Thanks,
Sean

 

[dhookom]

Have you tried exporting a query with a column like:
ExpFieldA:Format([FieldA],"0.000000")

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jewelslee]

Sean,
I am having a similar problem. What I have noticed is a decimal value like this .005 is exported to a delimited text file in this format 5e-03 and a table value like this .292 is exported like this .29. I have a formatted export query like this " Format([RESULT].[value],"###.0000") AS value2" and it has not solved my problem.

 

[dhookom]

What do you see when you view the datasheet of the exporting query? Is your column left or right aligned? How/where are you opening the exported file? Have you viewed the exported file in notepad?

I just did a test with your expression and it exported as expected.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jewelslee]

I will give you all settings from the table, query and macro levels:

table: Result
column: Value
data type: Number
field Sizeouble
format:General Number
decimal places:auto
default value:0

export query: qryExport2Test

(the field of interest is value)

SELECT [RESULT].[tag], Format([RESULT].[date],"mm/dd/yyyy") AS date2, Format([RESULT].

,"00000") AS code2, [RESULT].[ltGt], Format([RESULT].[value],"###.0000") AS value2, [RESULT].[Extra Field]
FROM RESULT;

Export Specification:
File Name:Result.txt
Save as type:Text Files (*.txt,*.ccv,*.tab, *.asc)
File Formatted:Delimited
Field Delimiter: |
Text Qualifier:{none}
Date Order: MDY
Date Delimiter: /
Time Delimiter: :
Four Digit Years: Checked
Decimal Symbol: .
Field Name: tag, date2, code2,ltGt, value2, Extra Field
Save As:Export Specification Test2


Macro:
Action:TransferText
Transfer Type:Export Delimited
Specification Name: Export Specification Test2
Table Name:qryExport2Test
File Name:C:\Documents and Settings\Result.txt
Has Field Names:No

So I cannot get this to export correctly. Please offer any suggestions.

 

[dhookom]

No offense but I feel a bit like Katie Couric interviewing a politician (state governor) this morning. She asked probing questions that we all wanted to hear the answers to. The governor provided answers to a different set of questions, pretty much ignoring Katie's questions.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[reiman]

Duane,

Thanks for the responses. I haven't looked at this because the work I was doing was time sensitive and I completed the project. It took a couple of programmers to get the fractional piece correct without rounding. The problem is not with the format formula, it is with access doing a calculation like field1 - int(field1). Instead of returning the exact fraction with 4 decimal places it was returning the fraction with 9 decimal places, but not in every case only a subset. Also, when the formula is applied everything looks correct in the datasheet view until you export. When you do, you get the scientific equivlent of the number or it rounds depending on the specs of the table.

Anyway, like I said that project is over and some more powerful programmers than I figured it out. Thanks for your help. I will be using the some of the things you gave me in the future.

Sean