Field is being rounded in the export process

[breezett93]

I have a query that when I run it in Access, the value I get is .6875. I then use TransferText acExportDelim, , "QueryName", "Location.csv", True. After the export, when I view the document in Notepad, Notepad+, or Excel, the value I get is .68.

I even exported as .txt and got the same results.

Can someone help me figure out where this rounding is taking place and make it stop?

Thanks.

 

[Andrzejek]

Are you saying that you have this [tt]Location.csv[/tt] file, and one of the field has a value of .6875 (0.6875 ?) before you use TransferText in Access.
And after the use of TransferText, you have a value of .68 (0.68 ?) in the same [tt]Location.csv[/tt] file / field?


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[breezett93]

No, access is creating the file based on data in the query. The value in the query is .6875. The value in the exported doc is .68. I need it to not change.

 

[Andrzejek]

Not really an answer to your question, but... if you are not happy with the TransferText 'magic' Access provides, why not write a few lines of your own code and logic? This way you are in full control


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[breezett93]

I would just like the output of the query written to a csv; so that Excel can open it.

 

[Andrzejek]

In this case, I would just write straight into Excel from Access, and don't even bother with csv.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[breezett93]

Is that possible with VBA? This process is used by users that don't have direct access to queries and tables.

 

[Andrzejek]

Absolutely. A few lines of code in Access to start Excel, write any data into Excel from any table/query in Access, format cells/color text/group data/highlight/set borders/bold/underline/whatever you can do 'by-hand' in Excel you can do in VBA code to it. And at the end show your Excel to the user and just watch them smile

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[breezett93]

The code below from

https://stackoverflow.com/questions...ents-of-table-query-to-excel-using-access-vba

should work then. Do I need anything extra to make those declarations?

Function WriteToExcel()
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    Dim strPath As String
    Dim appXL As Excel.Application
    Dim wb As Excel.Workbook
    Dim wsSheet1 As Excel.Worksheet
    Dim i As Long
    '*************************************************
    'First stage is to take the first query and place it
    'On sheet1
    '*************************************************
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    strSQL = "SELECT * FROM query1"
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.MoveFirst

    Set appXL = CreateObject("Excel.Application")
    With appXL
        'Set wb = .Workbooks.Add '<--- to create a new workbook
        Set wb = .Workbooks.Open("c:\temp\Myworkbook.xlsx") '<--- to open an exisiting workbook

        .Visible = True
    End With

    Set wsSheet1 = wb.Sheets("sheet1")
    wsSheet1.Select
    For i = 0 To rst.Fields.Count - 1
        wsSheet1.ActiveCell.Offset(0, i).Value = rst.Fields(i).Name
    Next
    wsSheet1.Range("a2").CopyFromRecordset rst
    wsSheet1.Columns("A:Q").EntireColumn.AutoFit
    rst.Close
End Function

 

[combo]

Export directly to Excel file is an alternative to export to csv text file and next open in Excel:
[tt]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QueryName", strExcelPathAndFileName, True, "SheetName"[/tt]


combo

 

[Andrzejek]

Your code is a good start, but it needs some work.
First, in order for it to work, you need to add references to:
[ul]
[li]Microsoft ActiveX Data Object X.X Library[/li]
[li]Microsoft Excel XX.X Object Library[/li]
[/ul]
I would change this 'Function' (since it does not return any value) to a 'Sub' and pass certain parameters. This way you can (re)use it for different purposes:

Option Explicit

Private Sub Command1_Click()
    Call WriteToExcel("query1")
End Sub

Public Sub WriteToExcel(ByRef strTblQuery As String, Optional strPath As String = "")
    Dim Cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim appXL As New Excel.Application
    Dim wb As Excel.Workbook
    Dim strSQL As String
    Dim i As Long
    
    Set Cnn = CurrentProject.Connection
    
    strSQL = "SELECT * FROM " & strTblQuery
    rst.Open strSQL, Cnn  [green]', adOpenKeyset, adLockOptimistic, adCmdTableDirect[/green]
    rst.MoveFirst

    With appXL
        If Len(strPath) = 0 Then
            Set wb = .Workbooks.Add [green]'<--- to create a new workbook[/green]
        Else
            Set wb = .Workbooks.Open(strPath) [green]'<--- to open an exisiting workbook[/green]
        End If
        .Visible = True
    
        For i = 0 To rst.Fields.Count - 1
            .Sheets(1).Cells(1, i + 1) = rst.Fields(i).Name
        Next
    
        .Range("A2").CopyFromRecordset rst
        .Cells.EntireColumn.AutoFit
    End With
    
    rst.Close
    Set rst = Nothing
End Sub

This code is not a full-proof, you will need to modify it if you want to write into an existing Excel file, etc.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

breezett93, did that help you in any way...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[breezett93]

Yes, I was able to take everyones' suggestions and combine them into one.

Firstly, I am now using TransferSpreadsheet to export as an .xls because xls did not touch my rounding.

After the export, I then call this

Function ConvertToCSV(src_file As String, dest_file As String)
Dim csv_format As Integer

csv_format = 6



Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit
End Function

to convert back to csv because my program needs to import as csv.

Thank you for your help!

 

[Andrzejek]

Good for you
But, I am confused (nothing new...)

You have data in your Access DB that you can see in query1
You create a spreadsheet in Excel's xls file with the data from query1
You have Excel save it as CSV file, because...
"my [Access?] program needs to import as csv"

Is that right?
You start with the data in Access DB and you end up with the same data in... Access DB

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[breezett93]

The program that needs to import as csv is not Access nor Excel, but a different program. Sorry for the confusion.

The normal process was to export from Access and import in the other program, bypassing excel entirely. The other program did not like that .6875 was being rounded to .68 when exported to csv.
I tested exporting to a bunch of different formats, and xls worked the best by not touching the rounding and having no errors.

 

[lameid]

Coming in late here... You have a working solution, so it is up to you whether you want to explore these alternative investigations. I am a big fan of if it is not broke don't fix it. On the other hand there are often things that can be made better when (if) you have the time. But others with similar problems may find another solution below too so I figure it is worth posting.

I am curious if you are opening the query before you export it? Does it display .68 in the layout grid before export if so? - I know that is stupid but I have seen that be the difference in old versions.
Have you tried docmd.transfertext without opening the query first?

Other thoughts...

Adjust the query so the filed displays all the digits, save it so it has the layout change and try the export.
In the export text wizard I am thinking you can specify the numeric data type exported in an Export specifcation, does using an export specification solve the problem (optional parameter for docmd.transfertext)?
One other thought, use CDBL function to wrap whatever arithmetic expression you are doing to force the resultant data type and maybe bypass the export specification.

Those are the things I would have tried before writing code....

But Access can also write directly to a text file instead of automating Excel. That is a solution easily searched for... as I'd have to search for it too as I don't remember all that low level basic text file manipulation off the top of my head... I've had to do it a few times over the years... Always something weird where I can't cludge it to work otherwise. Once was a tagged hierarchal file I think... Sometimes Access really is not the ideal tool. BTW the XML files access will write or did at the time follows one particular structure and the receiver wanted the other to make it interesting.

 

[breezett93]

Sorry for the delay in responding. I appreciate you reaching out.

>I am curious if you are opening the query before you export it? Does it display .68 in the layout grid before export if so?
Yes, I did open up the query before exporting because I was running out of places to check what was wrong. The query results were correct at .6875. That told me that something in the export process was changing the number.

>Adjust the query so the filed displays all the digits, save it so it has the layout change and try the export.
I did add a Round() to the field to force it to 4 digits.

>In the export text wizard I am thinking you can specify the numeric data type exported in an Export specification, does using an export specification solve the problem (optional parameter for docmd.transfertext)?
One other thought, use CDBL function to wrap whatever arithmetic expression you are doing to force the resultant data type and maybe bypass the export specification.
I have never used the export text wizard. I'll have to take a look at that option.

>Sometimes Access really is not the ideal tool
I hear you. I am in the process of setting up SQL Server to then convert the whole back end.

 

[strongm]

>something in the export process

TrtansferText uses Windows' regional settings ...