Results 1 to 3 of 3

Thread: Filter query

  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a query which I am exporting to an Excel spreadsheet using Docmd.Transferspreadsheet. How can I apply a filter prior to creating the spreadsheet?

    Here is the code I am using. The bold line is where I would like to filter the query. I am able to apply a filter using the Docmd.OpenReport. Is there something similar for the Transferspreadsheet?

    Sub PrintReports(PrintMode As Integer)
    On Error GoTo Err_Preview_Click

    Dim PSName As String
    Dim Scheduler As String
    Dim Language As String
    Dim Filename As String
    Dim Filepath As String

    PSName = "strPSName = Forms!frmMenuReport!SelectPaysource"
    Scheduler = "strSchedLN = Forms!frmMenuReport!SelectScheduler"
    Language = "strLanguage = Forms!frmMenuReport!SelectLanguage"


    If IsNull(Me.ReportMenu) Then
    MsgBox "You must first select a 'Report' from the Report List.", , "Blank Sub Menu"
    Exit Sub
    End If

    Select Case Me!ReportMenu
    Case 1
    If IsNull(Me!SelectScheduler) Then
    DoCmd.OpenReport "rptReferralsbyScheduler", PrintMode
    Else
    DoCmd.OpenReport "rptReferralsbyScheduler", PrintMode, , Scheduler
    End If
    Case 2
    DoCmd.OpenReport "rptOverdue", PrintMode
    Case 3
    If IsNull(Me!SelectPaysource) Then
    DoCmd.OpenReport "rptReferralsbyPS", PrintMode
    Else
    DoCmd.OpenReport "rptReferralsbyPS", PrintMode, , PSName
    End If
    Case 4
    DoCmd.OpenReport "rptReferral15Reminder", PrintMode
    Case 5
    DoCmd.OpenReport "rptReferral21Reminder", PrintMode
    Case 6
    DoCmd.OpenReport "rptReferralsDue", PrintMode
    Case 7
    DoCmd.OpenReport "rptCompleteReferrals", PrintMode
    Case 8
    DoCmd.OpenReport "rptIncorrectPaysources", PrintMode
    Case 9
    DoCmd.OpenReport "rpt15DayReminders", PrintMode
    Case 10
    DoCmd.OpenReport "rpt21DayReminders", PrintMode
    Case 11
    If IsNull(Me!SelectPaysource) Then
    DoCmd.OpenReport "rptActiveReferrals", PrintMode
    Else
    DoCmd.OpenReport "rptActiveReferrals", PrintMode, , PSName
    End If
    Case 12
    If IsNull(Me!SelectLanguage) Then
    DoCmd.OpenReport "rptReferralByLanguage", PrintMode

    Else
    DoCmd.OpenReport "rptReferralByLanguage", PrintMode, , Language
    End If
    Case 13
    DoCmd.OpenReport "rptTwentyDay", PrintMode
    Case 14
    DoCmd.OpenReport "rptSADate", PrintMode
    Case 15
    DoCmd.OpenReport "rptClosedReferrals", PrintMode
    Case 16
    ' Get default database folder
    Filepath = GetOption("Default Database Directory")
    ' Make sure that it ends in a backslash
    If Not Right(Filepath, 1) = "\" Then
    Filepath = Filepath & "\"
    End If

    ' Append date to filename
    Filename = "Active Referrals - " & _
    Format(Date, "mmmm d, yyyy") & ".xls"

    If IsNull(Me!SelectPaysource) Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryActiveReferrals", Filepath & Filename, True
    Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryActiveReferrals", Filepath & Filename, True
    End If


    End Select

    Exit_Preview_Click:
    Exit Sub

    Err_Preview_Click:
    Resume Exit_Preview_Click

    End Sub
    Easy Access

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Afraid NOT

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryActiveReferrals", Filepath & Filename, True

    It can ONLY use a Saved Query

    You could however modify the saved Query, or another query based upon it in Code

    Something like

    Suppose You have a Query qryActiveReferralsTemp which is

    SELECT * FROM qryActiveReferrals


    You could add a bit of Code in the ELSE section of the IF

    Code:
    Else
     	Set qdfTemp=CurrentDB.OpenQueryDef("qryActiveReferralsTemp")
     	qdfTemp.SQL="Select * From qryActiveReferral Where ADD YOUR FILTER CRITERIA HERE "
     	qdfTemp.Close
     	DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryActiveReferralsTemp", Filepath & Filename, True
    End if


    Andrew

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you so much for your help Andrew, you saved me many hours of aggravation & frustration.
    Easy Access

Posting Permissions

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