• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

parameterized query usage

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?
3 Solutions
Dale FyeCommented:
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:




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.
mlcktmguyAuthor 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()
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Dale FyeCommented:
Yes, except

Set rs = qd.openrecordset
mlcktmguyAuthor Commented:
Thank you,

How is qd dimensioned?

Dim qd as .........
Dale FyeCommented:
Dim qd as dao.querydef
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.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now