none
How to apply a filter to an append query RRS feed

  • Question

  • Referring to the command-click subroutine below, how can I adapt the code to apply a filter to the INSERT query?:

    >Private Sub Command_Click()
    >
    >Dim strSQL As String
    >
    >strSQL = "INSERT INTO dbo_ReportTable " & _
    >               "SELECT * FROM qryMyQuery;"
    >
    >DoCmd.RunSQL strSQL
    >
    >End Sub

    Briefly, here's how my app works:

    I have two forms:  a search form and a search results form with a filter on it.

    When the user presses a SEARCH button on the search form, I build a filter using a public function, and open a search results form with the filter.

    >DoCmd.OpenForm "frmSearchResults", acNormal, , Replace(BuildFilter, "WHERE", ""), , acDialog


    The search results form is based on the underlying "qryMyQuery".  On the search results form, I want a button to dump the current filtered resultset into a physical table.  Hence the INSERT INTO SELECT query.

    I guess the filter could be gotten from the search results form Filter property, or from the public function that I built previously in the calling search form.  I just don't know how to put the syntax together.

    Thanks.

    Thursday, June 2, 2016 9:00 PM

Answers

  • Hi. If your query does not have any criteria and sort order, you might be able to simply add the filter at the end. For example:

    strSQL = "INSERT... " & _

         "SELECT * FROM qryMyQuery " & BuildFilter()

    Or something like that. Hope it helps...

    • Marked as answer by District9 Thursday, June 2, 2016 10:50 PM
    Thursday, June 2, 2016 9:06 PM

All replies

  • Hi. If your query does not have any criteria and sort order, you might be able to simply add the filter at the end. For example:

    strSQL = "INSERT... " & _

         "SELECT * FROM qryMyQuery " & BuildFilter()

    Or something like that. Hope it helps...

    • Marked as answer by District9 Thursday, June 2, 2016 10:50 PM
    Thursday, June 2, 2016 9:06 PM
  • Well even though the calling search form is open, and the function that I need is defined as public, I had to refer to it explicitly like:

            strSQL = "INSERT INTO dbo_ReportTemp " & _
            "SELECT * FROM qryMyQUERY " & Forms("frmSearch").BuildFilter

    Now I just need to figure out how to deal with key violations in the SQL ReportTemp table.

    Thanks for your help!

    Thursday, June 2, 2016 10:49 PM
  • Hi. You're welcome. Glad to hear you got it figured out. Good luck with your project.
    Friday, June 3, 2016 3:24 PM