×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Field is being rounded in the export process

Field is being rounded in the export process

Field is being rounded in the export process

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

RE: Field is being rounded in the export process

Are you saying that you have this Location.csv 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 Location.csv file / field?
ponder

---- Andy

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

RE: Field is being rounded in the export process

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

RE: Field is being rounded in the export process

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
pc2

---- Andy

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

RE: Field is being rounded in the export process

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

RE: Field is being rounded in the export process

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

RE: Field is being rounded in the export process

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

RE: Field is being rounded in the export process

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 smile

---- Andy

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

RE: Field is being rounded in the export process

(OP)
The code below from https://stackoverflow.com/questions/23547122/writi... should work then. Do I need anything extra to make those declarations?

CODE

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 

RE: Field is being rounded in the export process

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

combo

RE: Field is being rounded in the export process

Your code is a good start, but it needs some work.
First, in order for it to work, you need to add references to:
  • Microsoft ActiveX Data Object X.X Library
  • Microsoft Excel XX.X Object Library
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:

CODE

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  ', adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.MoveFirst

    With appXL
        If Len(strPath) = 0 Then
            Set wb = .Workbooks.Add '<--- to create a new workbook
        Else
            Set wb = .Workbooks.Open(strPath) '<--- to open an exisiting workbook
        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

RE: Field is being rounded in the export process

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

---- Andy

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

RE: Field is being rounded in the export process

(OP)
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

CODE

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!

RE: Field is being rounded in the export process

Good for you thumbsup2
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 ponder

---- Andy

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

RE: Field is being rounded in the export process

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

RE: Field is being rounded in the export process

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.

RE: Field is being rounded in the export process

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

RE: Field is being rounded in the export process

>something in the export process

TrtansferText uses Windows' regional settings ...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close