parameterized query usage

mlcktmguy
mlcktmguy used Ask the Experts™
on
Been using Access for many years but though EE I just recently found out about parameterized queries.

I understand the concept but I'm not sure of the code to invoke the query with the paramaters.

I set up a query 'qryTest_Paramters' that will select data from table 'tblFees' based on the 'dateadded' field.

In the query, as criterion on the date added field I have 'Between [RangeFromDate] And [RangeEndDate].

I then set up RangeFromDate and RangeEndDate as parameters with a type of Date/Time.

When I run the query manually it prompts me to enter RangeFromDate and RangeEndDate and the resultant query shows the records based on the filter.

Now I want to invoke this query form VBA code behind a form.  The user enters two fields on the form BeginDate and EndDate.

In this case I want to create an export of the filtered data form the query.

Currently I have a transferspreadsheet command that exports the entire file, not just the filtered records

docmd.transferspreadsheet acexport, acspreadsheettypeExcel8, "qryXXXX", gselectedfolder

I would like to change "qryxxx" in that statement to export only filtered data from query 'qryTest_Paramter'

What is the syntax for using the parameterized query?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
You cannot actually do it that way (TransferSpreadsheet) with a parameter query unless you define your parameters to actually look at the form your users are using to enter those dates. In that case, the parameters in your query should look like:

[Forms]![yourFormName]![txtFromDate]

and

[Forms]!yourFormName]![txtFromDate]

subsituting the name of your form and controls into those parameters.  You should also declare those as your parameters rather than [RangeFromDate] And [RangeEndDate].  If you run the query manually, and that form is open, it will take the values from the controls on the form.  If the form is not open, it will ask you for the parameters.
you would need two text boxes for the params, then a string variable to hold the sql, then embed the params in the string
e.g. "select * where dbfield1=#" & txtbox1 & "# etc etc
remember to put hashes around date params.

Author

Commented:
Thank you, I can see how it's different in the trasferspreadsheet scenario.

In general have I defined the query correctly to use as a recordset as in the code below?  This is some example code I saw on EE with my vars inserted

Set qd = CurrentDB.QueryDef("qryTest_Paramters")
qd.Parameters("RangeFromDate") = begindate;
qd.Parameters("RangeEndDate") = endDate;

Set rs = qd.open()
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Yes, except

Set rs = qd.openrecordset

Author

Commented:
Thank you,

How is qd dimensioned?

Dim qd as .........
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Dim qd as dao.querydef
Distinguished Expert 2017
Commented:
You would not open the recordset first if what you wanted to do was to export to Excel.  Just create a saved querydef that references form controls.  In the code that runs the export, verify that the parameters have values and then just execute the TransferSpreadsheet normally.

coachman suggested building the SQL string in code.  I only do that if the string is dynamic.  As long as the query is static (no part of it changes), I use a querydef.  The querydef is compiled when it is first executed and the execution plan is stored so it is reused whenever you run the query.  Having parameters does not change a query from Static to Dynamic.  Keep in mind that dynamic SQL must be compiled EVERY time it executes and so it is less efficient than a querydef.  You wouldn't see a dramatic difference but if it is something that is executed many times by many people over the course of a day, the extra time adds up.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial