Results 1 to 4 of 4
  1. #1
    kaptin is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    2

    Date/time export to CSV file


    I have a need to export data from a table to a CSV file. I need one field to display only Short Date and two other fields to display only medium time. At present, despite formatting the fields appropriately in the table I get complete date and time in all instances in the CSV file. How can I define the data appropriately. The CSV file is subsequently loaded into another system which requires the Data in the formats Specified

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Try running a query instead of extracting directly from your table. Format the dates and times in your query and export the query as a .csv

    Alan

  3. #3
    kaptin is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    2
    Quote Originally Posted by alansidman View Post
    Try running a query instead of extracting directly from your table. Format the dates and times in your query and export the query as a .csv

    Alan
    Thanks Alan,
    I am using Access 2010. Tried your suggestion but the problem persists.
    If I export to Excel 2010 then that Fine, but my customer only has Office 2003 and that copy of Excel is incompatible with the System to which the data is uploaded.
    Would appreciate any alternative ideas as I have to fix this!!

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    How are you exporting. You can select which version of Excel you wish to export to. I run 2007 and export to earlier versions all the time.

    Here is some code that you can attach to a command button that will export a query to earlier excel versions

    Code:
    Private ExportQuery_Click()
    On Error GoTo Err_ExportQuery_Click
    Dim reportName As String
    Dim theFilePath As String
    
    reportName = "MonthlyActivity"    
    theFilePath = Type where you want your file exported to here.
    theFilePath = theFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
          
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, theFilePath, True
    
    MsgBox "Your report has been exported as and Excel Spreadsheet"
    
       
    
    Exit_ExportQuery_Click:
        Exit Sub
    
    Err_ExportQuery_Click:
        MsgBox Err.Description
        Resume Exit_ExportQuery_Click
        
    End Sub
    The "acSpreadsheetTypeExcel9" tells it to export as version 97-2003. Because you are using 2010, it probably says 14 where my code says 9. You will have to change the name of your query from my "Monthly Activity" to the name of your query.

    Question: If you client is running 2003 and you are in 2010, have you made allowances for the Access db to run in 2003?

    Good Luck.

    Alan

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 02-22-2011, 03:39 PM
  2. Replies: 6
    Last Post: 01-04-2011, 05:43 PM
  3. File name with date and Time suffix
    By keyies in forum Access
    Replies: 0
    Last Post: 04-29-2010, 05:10 AM
  4. File Export
    By Kencao in forum Import/Export Data
    Replies: 3
    Last Post: 02-01-2010, 05:27 PM
  5. How do you export a file with a unique file name
    By Budman42 in forum Import/Export Data
    Replies: 1
    Last Post: 10-15-2006, 06:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums