How to pass PARAMETERS to query from another query in MS ACCESS (without VBA)?

Question

In MS Access i have a query with long logic around start and end date (let's call it query_1). When you run the query you get asked for start date and end date:

PARAMETERS start_date datetime, end_date datetime;

query_1 is a 'base' query. I want to create many queries that get some information from that query_1. How can i supply the start_date and end_date to query_1 from the other queries?

I tried doing it with this, but it didn't work.

Where query_1.start_date = ## and query_1.end_date = ##

Any ideas how to do it?

=== Edit:

query1

PARAMETERS id number;
SELECT * FROM table1
WHERE table1.id = id

query2:

SELECT * FROM query1

result: asked 1 time for id

PARAMETERS id number;
SELECT * FROM query1

result: asked 1 time for id

SELECT * FROM query1
WHERE id = 13

result: asked 1 time for id

PARAMETERS id number;
SELECT * FROM query1
WHERE id = 13

result: asked 1 time for id

PARAMETERS id number;
SELECT * FROM query1
WHERE query1.id = 13

result: asked 1 time for id

PARAMETERS id number;
SELECT * FROM query1
WHERE query1.id = id

result: asked 1 time for id

PARAMETERS id1 number;
SELECT * FROM query1
WHERE query1.id = id1

result: asked 1 time for id, and 1 time for id1 (most provide identical in both)

PARAMETERS id1 number;
SELECT * FROM query1
WHERE query1.id = query2.id1

result: asked 1 time for id, 1 time for id1, and 1 time for query2.id

PARAMETERS query1.id1 number;
SELECT * FROM query1
WHERE query1.id = query2.id1

result: asked 1 time for id, 1 time for id1, and 1 time for query2.id

Gustav · Accepted Answer · 2020-04-26 08:47:31Z

1

Access will pass the request to the "base" query, so just specify the parameters the usual way:

PARAMETERS start_date datetime, end_date datetime;
Select * From query_1 Where query_1.start_date = start_date and query_1.end_date = end_date

You may have to rename the parameters or the query fields to avoid confusion.

For fixed parameters:

Select * From query_1 Where query_1.start_date = #2019/01/01# and query_1.end_date = #2019/12/31#

Edit:

The above won't work as Access always will try first to resolve the parameters, then run the query with whatever filter is applied.

What you can do, is to set the parameter first, then open the query:

DoCmd.SetParameter "id1", 13
DoCmd.OpenQuery "query2"

Another option is to check out TempVars and apply these in query1.

How to create TempVars from a form and use them in a query, using VBA

edited Apr 26, 2020 at 8:47

answered Apr 22, 2020 at 14:01

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

sql_noobe Over a year ago

can it be done without needing to provide the parameters each time? for example, "2019 query", that always sets start_date = #1/1/2019# and end_date = #12/31/2019# without asking the user for the dates?

sql_noobe Over a year ago

it doesn't work (For fixed parameters). i'm still get asked to provide the parameters very time. Access 2010.

Gustav Over a year ago

You may have to reveal your queries.

Gustav Over a year ago

Try using valid syntax: PARAMETERS id Long;

sql_noobe Over a year ago

Changed the parameters type to Long. it didn't help. still get prompted for id every time.

|

Collectives™ on Stack Overflow

How to pass PARAMETERS to query from another query in MS ACCESS (without VBA)?

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related