How do you pass parameters between queries in Microso

[Nick 1136]

I would like to achieve this without writing VB if possible. I have a form
(FORM1) and two queries (QUERY1, QUERY2).



FORM1 has two Text Boxes (TB_START_DATE, TB_END_DATE) and a Button (RUN), a
user retrieves formatted records within a particular date range by entering
the start of the range in TB_START_DATE (e.g. 12/08/2007) and the end of the
range in TB_END_DATE (e.g. 01/11/2007) and then clicks the run button, this
will return records between (inclusive) dates 12/08/2007 and 01/11/2007.



QUERY1 references the text boxes in FORM1 where a data range is entered.
Referencing the text boxes should allow QUERY1 to obtain the values entered
in the form, whereas it then executes QUERY2 by passing it these values to
satisfy its parameters. This results in records of a particular date range
being retrieved, which QUERY1 then formats in its select statement.



QUERY2 takes a date range as parameters (StartDate, EndDate) and returns a
set of time stamped records between (inclusive) the date range.





Example of initial attempt made:





TABLE1

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



Table 1 below shows an example of the source QUERY2 uses to retrieve records.



------------------------------------------------------------------------------------------------------------

Table 1

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

RECORD_ID DATE_CREATED RECORD_DESC

------------------------------------------------------------------------------------------------------------

1 26/10/2007 DESCPT_1

2 27/10/2007 DESCPT_2

3 28/10/2007 DESCPT_3

4 29/10/2007 DESCPT_4

5 30/10/2007 DESCPT_5

6 31/10/2007 DESCPT_6

7 01/11/2007 DESCPT_7

8 02/11/2007 DESCPT_8

9 03/11/2007 DESCPT_9

10 04/11/2007 DESCPT_10

------------------------------------------------------------------------------------------------------------





QUERY1

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



SELECT RECORD_ID, FORMAT(DATE_CREATED, "dd/mm/yyyy", RECORD_DESC

FROM QUERY2

WHERE ([StartDate] = [Form]![FORM1]![TB_START_DATE]) AND ([EndDate] =
[Form]![FORM1]![TB_END_DATE]);





QUERY2

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



PARAMETERS StartDate DateTime, EndDate DateTime;

SELECT RECORD_ID, DATE_CREATED, RECORD_DESC

FROM TABLE1

WHERE DATE_CREATED

BETWEEN [StartDate] AND [EndDate];

 

[Jeff Boyce]

Nick

Just to clarify...

It sounds like you might have one query (your Q2) that depends on the
results of another query (your Q1). If that's the case, you don't need to
"pass the parameters", since the first query (Q1) has already used them
(from your form) to get its results.

If Q2 is totally independent of Q1, then you STILL don't need to "pass the
parameters". Open Q2 in design view, then put references to the form's
controls in the selection criteria IN Q2.

Note that using a form to provide parameters to queries only works if your
form is open and the controls have values entered.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to achieve this without writing VB if possible. I have a
form
(FORM1) and two queries (QUERY1, QUERY2).



FORM1 has two Text Boxes (TB_START_DATE, TB_END_DATE) and a Button (RUN),
a
user retrieves formatted records within a particular date range by
entering
the start of the range in TB_START_DATE (e.g. 12/08/2007) and the end of
the
range in TB_END_DATE (e.g. 01/11/2007) and then clicks the run button,
this
will return records between (inclusive) dates 12/08/2007 and 01/11/2007.



QUERY1 references the text boxes in FORM1 where a data range is entered.
Referencing the text boxes should allow QUERY1 to obtain the values
entered
in the form, whereas it then executes QUERY2 by passing it these values to
satisfy its parameters. This results in records of a particular date
range
being retrieved, which QUERY1 then formats in its select statement.



QUERY2 takes a date range as parameters (StartDate, EndDate) and returns a
set of time stamped records between (inclusive) the date range.





Example of initial attempt made:





TABLE1

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



Table 1 below shows an example of the source QUERY2 uses to retrieve
records.



------------------------------------------------------------------------------------------------------------

Table 1

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

RECORD_ID DATE_CREATED RECORD_DESC

------------------------------------------------------------------------------------------------------------

1 26/10/2007 DESCPT_1

2 27/10/2007 DESCPT_2

3 28/10/2007 DESCPT_3

4 29/10/2007 DESCPT_4

5 30/10/2007 DESCPT_5

6 31/10/2007 DESCPT_6

7 01/11/2007 DESCPT_7

8 02/11/2007 DESCPT_8

9 03/11/2007 DESCPT_9

10 04/11/2007 DESCPT_10

------------------------------------------------------------------------------------------------------------





QUERY1

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



SELECT RECORD_ID, FORMAT(DATE_CREATED, "dd/mm/yyyy", RECORD_DESC

FROM QUERY2

WHERE ([StartDate] = [Form]![FORM1]![TB_START_DATE]) AND ([EndDate] =
[Form]![FORM1]![TB_END_DATE]);





QUERY2

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



PARAMETERS StartDate DateTime, EndDate DateTime;

SELECT RECORD_ID, DATE_CREATED, RECORD_DESC

FROM TABLE1

WHERE DATE_CREATED

BETWEEN [StartDate] AND [EndDate];

 

[KARL DEWEY]

All you need is one query like this --
SELECT RECORD_ID, DATE_CREATED, RECORD_DESC
FROM TABLE1
WHERE DATE_CREATED BETWEEN [Form]![FORM1]![TB_START_DATE]) AND
[Form]![FORM1]![TB_END_DATE]);

--
KARL DEWEY
Build a little - Test a little

I would like to achieve this without writing VB if possible. I have a form
(FORM1) and two queries (QUERY1, QUERY2).



FORM1 has two Text Boxes (TB_START_DATE, TB_END_DATE) and a Button (RUN), a
user retrieves formatted records within a particular date range by entering
the start of the range in TB_START_DATE (e.g. 12/08/2007) and the end of the
range in TB_END_DATE (e.g. 01/11/2007) and then clicks the run button, this
will return records between (inclusive) dates 12/08/2007 and 01/11/2007.



QUERY1 references the text boxes in FORM1 where a data range is entered.
Referencing the text boxes should allow QUERY1 to obtain the values entered
in the form, whereas it then executes QUERY2 by passing it these values to
satisfy its parameters. This results in records of a particular date range
being retrieved, which QUERY1 then formats in its select statement.



QUERY2 takes a date range as parameters (StartDate, EndDate) and returns a
set of time stamped records between (inclusive) the date range.





Example of initial attempt made:





TABLE1

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



Table 1 below shows an example of the source QUERY2 uses to retrieve records.



------------------------------------------------------------------------------------------------------------

Table 1

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

RECORD_ID DATE_CREATED RECORD_DESC

------------------------------------------------------------------------------------------------------------

1 26/10/2007 DESCPT_1

2 27/10/2007 DESCPT_2

3 28/10/2007 DESCPT_3

4 29/10/2007 DESCPT_4

5 30/10/2007 DESCPT_5

6 31/10/2007 DESCPT_6

7 01/11/2007 DESCPT_7

8 02/11/2007 DESCPT_8

9 03/11/2007 DESCPT_9

10 04/11/2007 DESCPT_10

------------------------------------------------------------------------------------------------------------





QUERY1

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



SELECT RECORD_ID, FORMAT(DATE_CREATED, "dd/mm/yyyy", RECORD_DESC

FROM QUERY2

WHERE ([StartDate] = [Form]![FORM1]![TB_START_DATE]) AND ([EndDate] =
[Form]![FORM1]![TB_END_DATE]);





QUERY2

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



PARAMETERS StartDate DateTime, EndDate DateTime;

SELECT RECORD_ID, DATE_CREATED, RECORD_DESC

FROM TABLE1

WHERE DATE_CREATED

BETWEEN [StartDate] AND [EndDate];