Passing parameter values between queries

[wellington]

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?

 

[ChrisJ]

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?

 

[wellington]

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?

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.

 

[ChrisJ]

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?

 

[wellington]

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)

 

[Tom Lake]

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

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

Tom Lake

 

[Knox]

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

 

[ChrisJ]

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]

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!

 

[wellington]

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 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)

Thank you for your response. I used your solution first as it seemed to be
the simplest. It worked!

 

[wellington]

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.

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)

Thank you for your response. I used the Knox solution first as it seems to
be the simplest. It worked!

 

[Knox]

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

:

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)

Thank you for your response. I used your solution first as it seemed to be
the simplest. It worked!

You're welcome.

I only have patience for simple solutions.

Knox