Passing parameter values between queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How does you pass parameters entered in one query on to another query? e.g.
query_A accepts a date value input_date, how do I pass this value to query_B?
 
What is the link between A and B. Are they both in the same macro?, are they
both being run from the same button on a form? Is B a sub query of A? How are
you setting the date for query A?
 
ChrisJ said:
What is the link between A and B. Are they both in the same macro?, are they
both being run from the same button on a form? Is B a sub query of A? How are
you setting the date for query A?
Click to expand...
query_A accepts a date value keyed by the user. query_B uses the output
from query_A to provide summary information. There are no forms or reports
involved.
 
So, confirming the procedure...

The user opens the database
Goes to the query window
Selects Query A
Query A has a parameter query that requests a date
User enters that date
Query A runs

Some time later
User selects query B...
and you want to use the same date that was used for query A

Is this the sequence of events?
 
ChrisJ said:
So, confirming the procedure...

The user opens the database
Goes to the query window
Selects Query A
Query A has a parameter query that requests a date
User enters that date
Query A runs

Some time later
User selects query B...
and you want to use the same date that was used for query A

Is this the sequence of events?
Not Quite
Click to expand...
The user opens the database
Goes to the query window
Selects Query B
Query B calls (uses as if it were a table say) Query A
Query A asks the user to enter a date value
I would then like to see (access) the date entered in Query A from Query B
(Sorry about the confusion)
 
Not Quite
The user opens the database
Goes to the query window
Selects Query B
Query B calls (uses as if it were a table say) Query A
Query A asks the user to enter a date value
I would then like to see (access) the date entered in Query A from Query B
Click to expand...

I'd use an unbound form to hold the parameter(s) and reference the form from
each macro.

Tom Lake
 
Here's my suggestion. Put as one of the return select values the same
string as you prompt for the date in query A. Then B would be able to use
mydate to show the user. The trick is having the string match exactly. If
you do that, Access will only prompt once, but use the same value in both
places. Here's the sample SQL:

SELECT Transactions.ACCT, Transactions.date, [enter the date] AS mydate
FROM Transactions
WHERE (((Transactions.date)>[enter the date]));

Then query B can use mydate which is returned with the date you entered.


Good luck,


Knox
 
The other two suggestions should work, but let's try a little lateral thinking.
You have queryB that contains a parameter criteria, something like

Where Transdate = [Please enter date]

This will return a bunch of records where transdate equals the date entered.

This date column can be used in QueryA, not in the criteria selection but in
the linking section. Something like

Select * from table1 inner join queryB on
table1.PK = queryB.PK
AND table1.Transdate = QueryB.Transdate

This should solve your problem.
 
Tom Lake said:
I'd use an unbound form to hold the parameter(s) and reference the form from
each macro.

Tom Lake


Thank you for your response. I used the Knox solution first as it seems to be the simplest. It worked!
Click to expand...
 
Knox said:
Here's my suggestion. Put as one of the return select values the same
string as you prompt for the date in query A. Then B would be able to use
mydate to show the user. The trick is having the string match exactly. If
you do that, Access will only prompt once, but use the same value in both
places. Here's the sample SQL:

SELECT Transactions.ACCT, Transactions.date, [enter the date] AS mydate
FROM Transactions
WHERE (((Transactions.date)>[enter the date]));

Then query B can use mydate which is returned with the date you entered.


Good luck,


Knox










wellington said:
The user opens the database
Goes to the query window
Selects Query B
Query B calls (uses as if it were a table say) Query A
Query A asks the user to enter a date value
I would then like to see (access) the date entered in Query A from Query B
(Sorry about the confusion)
Click to expand...
Click to expand...
Thank you for your response. I used your solution first as it seemed to be
the simplest. It worked!
 
ChrisJ said:
The other two suggestions should work, but let's try a little lateral thinking.
You have queryB that contains a parameter criteria, something like

Where Transdate = [Please enter date]

This will return a bunch of records where transdate equals the date entered.

This date column can be used in QueryA, not in the criteria selection but in
the linking section. Something like

Select * from table1 inner join queryB on
table1.PK = queryB.PK
AND table1.Transdate = QueryB.Transdate

This should solve your problem.

wellington said:
Not Quite
The user opens the database
Goes to the query window
Selects Query B
Query B calls (uses as if it were a table say) Query A
Query A asks the user to enter a date value
I would then like to see (access) the date entered in Query A from Query B
(Sorry about the confusion)
Click to expand...
Click to expand...
Thank you for your response. I used the Knox solution first as it seems to
be the simplest. It worked!
 
wellington said:
Knox said:
Here's my suggestion. Put as one of the return select values the same
string as you prompt for the date in query A. Then B would be able to
use
mydate to show the user. The trick is having the string match exactly.
If
you do that, Access will only prompt once, but use the same value in both
places. Here's the sample SQL:

SELECT Transactions.ACCT, Transactions.date, [enter the date] AS mydate
FROM Transactions
WHERE (((Transactions.date)>[enter the date]));

Then query B can use mydate which is returned with the date you entered.


Good luck,


Knox










wellington said:
:

So, confirming the procedure...

The user opens the database
Goes to the query window
Selects Query A
Query A has a parameter query that requests a date
User enters that date
Query A runs

Some time later
User selects query B...
and you want to use the same date that was used for query A

Is this the sequence of events?
Not Quite
The user opens the database
Goes to the query window
Selects Query B
Query B calls (uses as if it were a table say) Query A
Query A asks the user to enter a date value
I would then like to see (access) the date entered in Query A from
Query B
(Sorry about the confusion)
Click to expand...
Click to expand...
Thank you for your response. I used your solution first as it seemed to be
the simplest. It worked!
Click to expand...

You're welcome.

I only have patience for simple solutions. :)

Knox
 
Back
Top