Export with Format

[Scott]

When I use the following script to export a table to a CSV file I loose some
formating. There is one filed that is formated as SINGLE, Fixed, 5 dec.
When the export runs i get some scientific notation. (depending on how it
was stored). Is there any way to retain formating. Or is there anyway to
force the formating in the table.

Script

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim strSQL As String
Dim n As Long, i As Integer
Dim DESTINATIONFOLDER As String
Dim qty_PartMTL As Integer

DESTINATIONFOLDER = TXT_PartMTL_Path.Value & "\"
qty_PartMTL = TXT_PartMTL_QTY.Value
Set dbs = CurrentDb
For n = 1 To 60000 Step qty_PartMTL
i = i + 1

strSQL = "SELECT * FROM [SC - PartMtl] WHERE [Number01]>= " & n & "
AND [Number01] < " & n + qty_PartMTL

Set qdf = dbs.CreateQueryDef("qryTemp_PartMTL", strSQL)
DoCmd.TransferText acExportDelim, , "qryTemp_PartMTL", _
DESTINATIONFOLDER & "PartMTL" & i & ".csv", True
dbs.QueryDefs.Delete "qryTemp_PartMTL"

Next n

 

[John W. Vinson]

When I use the following script to export a table to a CSV file I loose some
formating. There is one filed that is formated as SINGLE, Fixed, 5 dec.
When the export runs i get some scientific notation. (depending on how it
was stored). Is there any way to retain formating. Or is there anyway to
force the formating in the table.

The Format property of a table field applies only to screen display within
Access, not to exporting. What you can do is create a Query based on the table
and use the Format() function to explicitly cast the field as a string:

ExpNumber: Format([fieldname], "#.00000")

and export from the query, rather than from the table.

 

[Scott]

I did this, but does not work on 123.45678. I can have between 1 and 3 hole
numbers and up to a 5 position decimal.

QtyPer1: Format([qtyper],"#.00000")

 

[John W. Vinson]

I did this, but does not work on 123.45678. I can have between 1 and 3 hole
numbers and up to a 5 position decimal.

QtyPer1: Format([qtyper],"#.00000")

It's a Single? a Single has approximately 7 decimal places precision and will
lose the precision in the last digit. If you have the data in your table
already in Single, it's probably impossible to get all the digits!

Could you explain what "does not work"? What DO you get?

 

[Scott]

Thanks for getting back to me.

This is what I see in the field > 0.00769
This is format that I should have 1.43567 < this may not be the right
number but is the right format.

This is what I get in my out put > 7.692308e-03

I have approx 55,000 records and only about 2% of the output is in
scientific notation. The final table is has this field formated correctly
"This is what I see, from above".

Also I tried to create a query with this for my QTYPER field

QtyPer1: Format([qtyper],"#.00000")

The output is blank.#####

I need to have a leading 0 in the ones place. If the numer is 10.123 that
fine but if it is .123 it will fail the import process (my target system).
The target system needs to have ###,###.#####. I would only have at a max
###.##### filling the hundereds and 5 decimal places.

Thanks.

 

[John W. Vinson]

Thanks for getting back to me.

This is what I see in the field > 0.00769
This is format that I should have 1.43567 < this may not be the right
number but is the right format.

Is the > character in the "> 0.00769" you cite actually there?? If so it
CANNOT be a Number field! And if it is in fact 0.00769, why and how would you
expect it to automagically develop a value in the ones' place?

This is what I get in my out put > 7.692308e-03

I have approx 55,000 records and only about 2% of the output is in
scientific notation. The final table is has this field formated correctly
"This is what I see, from above".

Again: > is not a valid character in a Number field. If it is required in the
output file, you've got trouble!

Also I tried to create a query with this for my QTYPER field

QtyPer1: Format([qtyper],"#.00000")

The output is blank.#####

I need to have a leading 0 in the ones place. If the numer is 10.123 that
fine but if it is .123 it will fail the import process (my target system).
The target system needs to have ###,###.#####. I would only have at a max
###.##### filling the hundereds and 5 decimal places.

Try #,##0.00000 as a format, then. This will ensure that there is at least one
digit (a 0, if the field value is less than 1) in the ones' place, and a comma
if the number is over 1000; and it will contain five decimals.

 

[Scott]

why is this not a number?
0.03125

The QTYPER field will have any range of number for 200.0 to 0.00001

When I wrote > 0.03125 i used the ">" as a visual pointer.

I will try your previous usgestion today and get back you.

Again thank you for the help.

 

[John W. Vinson]

why is this not a number?
0.03125

It is.

The QTYPER field will have any range of number for 200.0 to 0.00001

When I wrote > 0.03125 i used the ">" as a visual pointer.

Sorry, that was my confusion.

I will try your previous usgestion today and get back you.

A Format of #0.00000 should work for you.

 

[Scott]

WOrked Greate

It is.


Sorry, that was my confusion.


A Format of #0.00000 should work for you.