10

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?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
HamburgIsNice
  • 105
  • 1
  • 10

2 Answers2

8

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:

NumbersTab.png

(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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Hey, I have a future question to the export function. I have to change the export format, because I need a semicolon instead of comma and a leading zero in dates. Is this possible to change this, when using this function? Otherwise I can't use this nice little function :( – HamburgIsNice Feb 28 '14 at 14:53
  • 1
    Amazing, the global settings work! Thank you so much! It's really a shame that MS Access has such a brain-damaged default for exporting floating points. Broken-by-design (TM) – Tomas Oct 07 '19 at 12:39
2

The following will produce 8 decimal places:

Dim dblFld      As Double

dblFld = 2197.5678
Debug.Print dblFld
Debug.Print Format(dblFld, "#,##0.00000000")
Wayne G. Dunn
  • 4,282
  • 1
  • 12
  • 24
  • 1
    Unfortunately, using `Format()` on a number turns it into a string, so Access will put double-quotes around it when it exports it to CSV (e.g., `,"2.5000000",`). – Gord Thompson Feb 22 '14 at 21:09
  • I just did some more testing and if you format as "0.00000000" (remove 'thousands' delimiter, then the number is exported to csv without the quotes. If the process that uses the csv can accept/format something like 1234.12000000 then this should work. @HamburgIsNice what are you doing with the CSV file? Will you be able to format the column to regain the thousands? ALso, importing to Excel loses the trailing zeros! – Wayne G. Dunn Feb 22 '14 at 22:55
  • How are you exporting the CSV? I just created a saved query as `SELECT Format(2.5,"0.00000") AS Expr1 FROM Dual;` and exported it as CSV using both (1) the Export wizard, and (2) `DoCmd.TransferText acExportDelim` and in both cases I got `"2.50000"`. – Gord Thompson Feb 22 '14 at 23:53
  • Also, when exporting a numeric value *trailing* zeros are really a non-issue. Rounding (or truncating) `3.14159` to `3.14` might be a problem, but `2.5` and `2.500` are the same numeric value. – Gord Thompson Feb 22 '14 at 23:58
  • I was using the code you provided, except on the SaveAs line, I hard-coded a path and file name that ended with .csv. Originally I just exported the number field, but then went back and added a text field on either side of the number. But Doh! I just noticed that although the fields are comma delimited, the text fields are NOT delimited by quotes.... back to the drawing board... – Wayne G. Dunn Feb 23 '14 at 00:53