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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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("qryTes
qd.Parameters("RangeFromDa
qd.Parameters("RangeEndDat
Set rs = qd.open()
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("qryTes
qd.Parameters("RangeFromDa
qd.Parameters("RangeEndDat
Set rs = qd.open()
Yes, except
Set rs = qd.openrecordset
Set rs = qd.openrecordset
ASKER
Thank you,
How is qd dimensioned?
Dim qd as .........
How is qd dimensioned?
Dim qd as .........
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
e.g. "select * where dbfield1=#" & txtbox1 & "# etc etc
remember to put hashes around date params.