Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Location
    USA
    Posts
    74

    Unanswered: export filtered records

    Hi,

    I created a form based on a query whereby users answer to a prompt and click a button. The report then displays records based on selection made. This works nicely.
    However, my attempt to export the filtered records to excel gives me everything (it doesn't reflect filtered data).

    Can you please assist?

    TIA

    Regards,

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    602
    Provided Answers: 31
    save the 'where' filter into a query,
    export the query...
    'a button to filter recs

    Code:
    '----------------
    sub btnFilter_click()
    '----------------
    dim sWhere as string 
    
    sWhere = getConditions()
    
    If sWhere = "1=1" Then
      Me.FilterOn = False
    Else
      Me.Filter = sWhere
      Me.FilterOn = True
    End If
    end sub
    
    
    'a button to export the query
    '----------------
    sub btnExport_click()
    '----------------
    dim qdf as querydef
    dim sWhere as string 
    dim vQry,vFile
    
    sWhere = getConditions()
    
    If sWhere = "1=1" Then
      vQry = "qsAllData"
    Else
      vQry = "qsDataFlt"
      set qdf = currentdb.querydefs(vQry)
      qdf.sql = "select * from table " & sWhere
      qdf.close
     
    
       'export
      vFile = "c:\my documents\" & vQry & ".xlsx"
     docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,vQry,vFile,true,vQry
    End If
    end sub
    
    
    'build the filter
    '----------------
    function getConditions()
    '----------------
    dim sWhere
    
    sWhere = "1=1"
    if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
    if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
    if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"
    
    getConditions=sWhere
    end function
    Last edited by ranman256; 08-30-17 at 10:45.

  3. #3
    Join Date
    Sep 2002
    Location
    USA
    Posts
    74
    ranman256, thank you for the reply post.

    I’m relatively new to vba and have couple of questions:
    When you say, “save the 'where' filter into a query” and
    “export the query...”

    1. Do I need to create separate buttons to place the code, or just place the code on click event of the cmdExport button?
    2. Do I need to re-construct the If Not IsNull…(your gave as an example) and replace [State], [City], [ZipCode] with my data elements and place it on click event of cmdExport?

    So far, I’ve two buttons:
    i. cmdRunQuery: to filter/run query which works beautifully (code attached)
    ii. cmdExport: to export to excel (code below)

    Private Sub cmdExport_Click()
    DoCmd.OutputTo acOutputQuery, "My_Query_Name", acFormatXLSX, , True
    End Sub

    The export works, but gives me everything. This is where I’m struggling and need assistance….


    TIA

    Regards,
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •