none
Dynamic query question... RRS feed

  • Question

  • I have a report in which I programatically build pieces of the SQL from a query and then use that as the Record Source for my report.  I'd like to take this same query and export it to Excel - again dynamically altering the SQL prior to execution.  Is it possible to change the query SQL prior to execution?

    This is a crosstab query and one of the things I need to build is the "IN" list.

    Thank you!


    Thanks for your help!!

    Wednesday, March 29, 2017 6:18 PM

Answers

  • For an export to Excel, you pretty much have to be exporting a stored query.  You can modify that query's SQL dynamically, before exporting it, using code along these lines:

        Dim strSQL As String

        stSQL = ...

        CurrentDb.QueryDefs("qryExport").SQL = strSQL

        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExport", "C:\MyFilePathAndName", True


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by plynton Wednesday, March 29, 2017 6:39 PM
    Wednesday, March 29, 2017 6:31 PM

All replies

  • For an export to Excel, you pretty much have to be exporting a stored query.  You can modify that query's SQL dynamically, before exporting it, using code along these lines:

        Dim strSQL As String

        stSQL = ...

        CurrentDb.QueryDefs("qryExport").SQL = strSQL

        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExport", "C:\MyFilePathAndName", True


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by plynton Wednesday, March 29, 2017 6:39 PM
    Wednesday, March 29, 2017 6:31 PM
  • Perfect - thanks, Dirk!

    Thanks for your help!!

    Wednesday, March 29, 2017 6:40 PM