Results 1 to 11 of 11
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    Export Delim File from Query

    Greetings All:

    I am trying to export a comma delim file from a query but keep getting a 3011 Error "could not find the object 'SELECT * FRom LarqData WHERE Date_Time > 12:00:00AM'.

    Code:
    Sub SerialExport()
    Dim strGetLastDateSQL, strGetDailyRecsSQL, strInsExpDateSQL As String
    Dim strFileName As String
    Dim dteLastExp As Date
    Dim dteTodayDate As String
    Dim oApp As New Outlook.Application
    Dim oEmail As Outlook.MailItem
    
    dteTodayDate = Format(Date, "MMDDYYYY")
    strFileName = "C:\Users\davei\Desktop\Database\LarqSerialExport_" & dteTodayDate
    strGetLastDateSQL = "SELECT Max(LastExport) As dteLastExp FROM ExpTracking"
    strGetDailyRecsSQL = "SELECT * FROM LarqData WHERE Date_Time >" & dteLastExp
    strInsExpDateSQL = "INSERT INTO ExpTracking (LastExport)#" & Now() & "#)"""
    
    DoCmd.SetWarnings False
    
    DoCmd.TransferText acExportDelim, "", strGetDailyRecsSQL, strFileName, False, ""
    CurrentDb.Execute strInsExpDateSQL
        
    
    CurrentDb.Execute strInsExpDateSQL
    
    DoCmd.SetWarnings True
    
    
    End Sub
    Will I need to query into an empty table, and then use the TransferText function? I am at a loss here. Any assistance would be greatly appreciated.

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Dates should be delimited with octothorpes (#).
    try
    Code:
    "SELECT * FROM LarqData WHERE Date_Time > #" & dteLastExp & "#"

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    transfertext transfers an object to a text file, so you need to create a query based on your sql and transfer the query

    another problem with your code - you don't assign a value to dteLastExp

    and another - you need to format dates US style as mm/dd/yyyy or yyyy-mm-dd when provided as text to use as sql. If you use US style date formats you are probably OK

  4. #4
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Now I am getting a syntax error on:

    Code:
    strGetDailyRecsSQL = "SELECT * FROM LarqData WHERE Format(Date_Time, "mm-dd-yyyy" => #" & Format(dteLastExp, "mm-dd-yyyy") & "#;"
    DO I need to format both dates the same? Also, does Date_Time have to be in octothorpes (#)?

  5. #5
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Disregard Syntax error

  6. #6
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    OK, I am confused Ajax. How do I build a query and pass it the date from strGetLastDateSQL? Or do I need to build a query for both and call the first from the second.

    I am beginning to think that it might be easier to just select into a temp table and and use the temp table as transfertext object?

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    you don't need to first format.

    You also seem to be mixing up date and now - now includes a time element

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    you don't need these separate queries

    strGetLastDateSQL = "SELECT Max(LastExport) As dteLastExp FROM ExpTracking"
    strGetDailyRecsSQL = "SELECT * FROM LarqData WHERE Date_Time >" & dteLastExp

    you can just use

    strGetDailyRecsSQL = "SELECT LarqData.*" & _
    " FROM LarqData, (SELECT Max(LastExport) As dteLastExp FROM ExpTracking) AS DLE" & _
    " WHERE LargData.Date_Time >DLE.dteLastExp"


    Have a query called say qryExpCsv

    and in your code

    currentdb.querydefs("qryExpCsv").sql=strGetDailyRe csSQL
    DoCmd.TransferText acExportDelim, "", "qryExpCsv", strFileName, False, ""

  9. #9
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Just so that I am clear, create a query call qryExpCsv with the Select statement above. Then, put the two lines in the code.

    If this is correct, I keep getting an Invalid SQL Statement on: currentdb.querydefs("qryExpCsv").sql=strGetDailyRe csSQL

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    for some reason the forum keep adding a space

    should be

    strGetDailyRecsSQL

    Having said that - if you have created the query with that sql, then no need for that line - just export using transfertext and update the table with the latest date/time

  11. #11
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Thanks for all of the help.

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

Similar Threads

  1. Export Query as Batch File
    By shank in forum Queries
    Replies: 12
    Last Post: 06-12-2019, 01:22 PM
  2. Can you Export Query as a New Tab on existing Excel file
    By ItsJustMe64 in forum Import/Export Data
    Replies: 2
    Last Post: 05-14-2019, 03:40 PM
  3. Export Multiple Query in one CSV file
    By adnancanada in forum Import/Export Data
    Replies: 5
    Last Post: 01-16-2018, 02:54 PM
  4. Export query to a csv file.
    By swedni in forum Import/Export Data
    Replies: 3
    Last Post: 01-13-2015, 08:01 AM
  5. Replies: 2
    Last Post: 10-15-2014, 02:23 AM

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