Number Formating

[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

 

[Clifford Bass]

Hi Scott,

Use the Format() function. For example:

select ..., Format([Single_Field], "0.00000") as
Single_Field_Formatted, ...

Clifford Bass

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