While I couldn't find a way to get the VBA code from the first post to properly set parameters without prompts, I found a different method that is achieving this.
Query1 SQL Statement with multiple parameters
Code:
PARAMETERS BeginDate DateTime, EndDate DateTime;
SELECT OpeningName, OpeningBuilding
FROM tblOpening
WHERE OpeningInspectionDate BETWEEN [BeginDate] AND [EndDate];
VBA Code Setting Saved Query Parameters and Exporting to Excel without Prompts
Code:
Private Sub ExportFile()
DoCmd.SetParameter "BeginDate", "#" & Format(txtBeginDate, "mm/dd/yyyy hh:nn") & "#"
DoCmd.SetParameter "EndDate", "#" & Format(txtEndDate, "mm/dd/yyyy hh:nn") & "#"
DoCmd.OpenQuery "Query1", acViewNormal, acReadOnly
DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLSX, , True, "", , acExportQualityPrint
DoCmd.Close acQuery, "Query1"
End Sub
One minor drawback to this method is Query1 must be opened in the process, and there is no acHidden option for opening queries. Another thing to note is there is no short date datatype for parameters, so when passing dates to the parameter, they must be converted to date and time first.
I am going to consider this problem solved, but I am still curious about the technique involving my first example using qdfExport.Parameters(0). I have seen several people post that method and claim that it worked so if anyone has any information or idea why it might not have been working for me I'd appreciate it.
Lastly, if anyone has any thoughts on a comparison between the two methods for exporting queries to excel, writing the SQL statements in VBA vs parameterizing saved queries, I'd appreciate those as well.
I'll be testing and comparing them and will let you know if I find anything interesting.
Thanks