CSV export of an Access query limits float (Single/Double) values to 2 decimal places

Question

I searching for hours to change the amount of decimal places to 8, instead of 2 with VBA Access. I found some solutions to change the system proppertys by using this tag:

Public Const LOCALE_ILZERO = &H12

but it doesen't work and I dont know how I could change this for my program, independent of the computer using this programm.

Could anyone help me?

Answer 1

After fighting with this issue myself and discovering that I could export the Access table/query to Excel and then save it as CSV from there, I automated the process like this:

Public Sub ExportToCsvViaExcel(ItemToExport As String, _
        DestinationFileSpec As String, _
        Optional IncludeFieldNames As Boolean = True)
    Const TemporaryFolder = 2
    Const xlUp = -4162
    Const xlCSVWindows = 23
    Dim xlApp As Object  ' Excel.Application
    Dim xlWorkbook As Object  ' Excel.Workbook
    Dim fso As Object  ' FileSystemObject
    Dim TempFileSpec As String

    Set fso = CreateObject("Scripting.FileSystemObject")
    TempFileSpec = fso.GetSpecialFolder(TemporaryFolder) & "\" & fso.GetTempName & ".xls"
    Set fso = Nothing
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ItemToExport, TempFileSpec, False
    Set xlApp = CreateObject("Excel.Application")
    Set xlWorkbook = xlApp.Workbooks.Open(TempFileSpec)
    If Not IncludeFieldNames Then
        xlApp.Rows("1:1").Select
        xlApp.Selection.Delete Shift:=xlUp
    End If
    xlWorkbook.SaveAs DestinationFileSpec, xlCSVWindows
    xlWorkbook.Close False
    Set xlWorkbook = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Kill TempFileSpec
End Sub

The above code doesn't force the number of decimal places to 8 (or any other number), it just outputs as many as there are.

As far as I know, the only way to change rounding/truncating behaviour for CSV exports from Access itself is to open the "Regional and Language Options" in the Windows Control Panel, click the "Customize this format..." button on the "Formats" tab, then change the "No. of digits after decimal" value on the "Numbers" tab:

(2 is a very common default value. In the above dialog I have changed it to 5.)

I'm not aware of any way to change that value from VBA, and I have my doubts that doing so would change the behaviour of the currently-running instance of Access anyway.

Answer 2

The following will produce 8 decimal places:

Dim dblFld      As Double

dblFld = 2197.5678
Debug.Print dblFld
Debug.Print Format(dblFld, "#,##0.00000000")