CODEWITHSUNDEEP
sqlms-accessparameters
sql_noobe
sql_noobe

Reputation: 55

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

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

Upvotes: 0

Views: 487

Answers (1)

Gustav
Gustav

Reputation: 55816

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

Upvotes: 1

Related Questions