Solved

parameterized query usage

Posted on 2014-03-06
7
221 Views
Last Modified: 2014-03-12
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?
0
Comment
Question by:mlcktmguy
7 Comments
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 39909970
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.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39909973
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.
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39910372
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()
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39910425
Yes, except

Set rs = qd.openrecordset
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39910613
Thank you,

How is qd dimensioned?

Dim qd as .........
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 39910627
Dim qd as dao.querydef
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 150 total points
ID: 39910732
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now