Solved

parameterized query usage

Posted on 2014-03-06
7
226 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 35

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

832 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