Results 1 to 7 of 7
  1. #1
    jmorasos is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    4

    Exporting specific fields from from results using SQL query is showing all fields.

    Hi guys,

    I am using a button to export form results to excel. The results showed are not all the fields in the table. For this example I want to export only the field [Operator TL] from the table "Data", including some criteria named "StrCriteria". When I click the button I does export to Excel, but it export all the fields in the table, no matter if I only requested [Operator TL]. Do you have any idea why is this happening? Below you may find the code VBA code:

    Private Sub Command357_Click()


    Dim strSQL As String
    Dim strQry As String
    Dim strFields As String
    Dim strCriteria As String


    strFields = "([Operator TL])"
    strCriteria = "([Is Feedback required] = TRUE and [Site] = '" & Me.Center & "' and [Error_Accountability] = '" & Me.ErrorCombo & "' and [SDL] = '" & Me.SDLCombo & "' and [Product] = '" & Me.ProductCombo & "' and [Mispost Date] >= #" & Me.Initial & "# and [Mispost Date] <= #" & Me.Final & "#)"
    strSQL = "Select (" & strFields & ") from Data where (" & strCriteria & ") order by [Mispost Date]"
    strQry = "TempQueryName"


    Set Db = CurrentDb
    Set Qdf = Db.CreateQueryDef(strQry, strSQL)

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
    strQry, "C:\Users\jmorasos\Documents\Test1.xls", True

    DoCmd.DeleteObject acQuery, strQry


    End Sub


    Best regards!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can use this to check the SQL:

    http://www.baldyweb.com/ImmediateWindow.htm

    personally I would get rid of all the parentheses around the field, though I don't know that it causes this problem. I'd also comment out the line deleting the query and see if it contains the expected SQL.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jmorasos is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    4
    Hi pbaldy,

    This is the code a bit simplified and without parentheses:

    Private Sub Command357_Click()


    Dim strSQL As String
    Dim strQry As String


    strCriteria = "[Site] = '" & Me.Center & "' and [Error_Accountability] = '" & Me.ErrorCombo & "'"
    strSQL = "Select [Operator TL] from Data where " & strCriteria & ""
    strQry = "TempQueryName"

    'Debug.Print strSQL


    Set Db = CurrentDb
    Set Qdf = Db.CreateQueryDef(strQry, strSQL)

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
    strQry, "C:\Users\jmorasos\Documents\Test1.xls", True

    DoCmd.DeleteObject acQuery, strQry


    End Sub

    I have tried to use the immediate window but nothing is happening. I click the (ctrl-break) button but the window is not being showed. The query in the code works, but is showing all the fields in the form as if I had selected the button "Export to Excel spreadsheet".


  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here (zip first)?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jmorasos is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    4
    Sorry, but I can not use ZIP

  6. #6
    jmorasos is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    4
    Actually, when I remove : DoCmd.DeleteObject acQuery, strQry, a Query is created and not deleted. when I run that Query, It runs well and gives me [Operator TL] only using the right criteria. The thing is that the code is exporting to excel all the information from the form and applying the criteria. The only thing missing is not to "Select *" but only the fields specified in the query.

    I just want to be able to export the split form results after filtering to an Excel spreadsheet. I think it should not be that hard.

    Any idea? Thank you!

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I tested something similar and got just the specified field. Have you deleted the Excel file, in case it's an old one that isn't getting overwritten?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 3
    Last Post: 02-20-2017, 08:27 AM
  2. Replies: 1
    Last Post: 02-23-2016, 08:36 PM
  3. Exporting Query with rtf Fields to Excel
    By EddieN1 in forum Import/Export Data
    Replies: 4
    Last Post: 05-25-2013, 10:51 AM
  4. Replies: 4
    Last Post: 05-14-2012, 06:10 PM
  5. Exporting Records to Specific Worksheets and Fields
    By Kapelluschsa in forum Import/Export Data
    Replies: 1
    Last Post: 02-28-2012, 02:11 PM

Tags for this Thread

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