Number Formating

  • Thread starter Thread starter Scott
  • Start date Start date
S

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
Click to expand...
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
 
Hi Scott,

Use the Format() function. For example:

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

Clifford Bass

Scott said:
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
Click to expand...
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
Click to expand...
 
Back
Top