Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

parameterized query usage

Posted on 2014-03-06
7
Medium Priority
?
245 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 400 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 48

Expert Comment

by:Dale Fye
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 48

Assisted Solution

by:Dale Fye
Dale Fye earned 400 total points
ID: 39910627
Dim qd as dao.querydef
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 600 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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