Pass parameter to a query from another query in Access

Question

I have a parameterized query GET_CUSTOMER:

SELECT * FROM Customer WHERE id = [customer_id]

I want to call this query from another query and pass it a parameter:

SELECT * FROM GET_CUSTOMER(123)

Note the above code is not valid, it is here to give you an idea of what I'm trying to do. Is it possible to do this in MS Access?

UPDATE 1:

The queries I posted are for example. The actual queries are much more complex. I know I can use table joins, but in my specific case it would be much easier if I could run parameterized queries inside other queries (that are parameterized as well). I can't use access forms because I'm using access with my .NET application.

Why? Both queries will return the same results? I mean the answer is yes. If you open a new query and do `Select GET_CUSTOMER.* FROM GET_CUSTOMER` it will still ask for the parameter for `id`. So you would pass it as if it was the original query. — engineersmnky, Jul 09 '14 at 20:59
Please always include all tags. For example, you should include which particular flavour of .net, in addition to my edit. — Fionnuala, Jul 10 '14 at 01:01
I didn't add .NET because it is irrelevant for my specific problem. My problem is to pass parameter to a query from another query in Access. — andr111, Jul 10 '14 at 17:35
@andr111: QueryDef objects in Access can see the Parameters of the queries they are built on. Say Query2 is the query that sits on top of GET_CUSTOMER; you can write code similar to this: QueryDefs("Query2").Parameters("customer_id") = 123, even though the param does not belong to the top level. So the params of the underlying query can therefore be accessed by the higher-level ones (off the top of my head, I don't know what happens if there are identically named parameters in the stack of queries; I think they are all given the same value once the param is set). So yes, it can be done. — VBlades, Jul 11 '14 at 22:39

score 4 · Accepted Answer · edited May 23 '17 at 11:43

This is how I end up solving this with help of https://stackoverflow.com/a/24677391/303463 . It turned out that Access shares parameters among all queries so there is no need to specifically pass parameters from one query to another.

Query1:

SELECT * FROM Customer WHERE ID > [param1] AND ID < [param2]

Query2:

SELECT * FROM Query1

VB.NET code:

    Dim ConnString As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=Database.mdb"
    Dim SqlString As String = "Query2"
    Using Conn As New OleDbConnection(ConnString)
        Using Cmd As New OleDbCommand(SqlString, Conn)
            Cmd.CommandType = CommandType.StoredProcedure
            Cmd.Parameters.AddWithValue("param1", "1")
            Cmd.Parameters.AddWithValue("param2", "3")
            Conn.Open()
            Using reader As OleDbDataReader = Cmd.ExecuteReader()
                While reader.Read()
                    Console.WriteLine(reader("ID"))
                End While
            End Using
        End Using
    End Using

score -1 · Answer 2 · answered Jul 09 '14 at 21:50

-1

You can build the SQL on the fly.

MyID = prompt or get from user some ID

strSQl = "Select * from tblCustomer where ID in " & _
       "(select * from tblTestCustomers where id = " & MyID

So you can nest, or use the source of one query to feed a list of ID to the second query.

answered Jul 09 '14 at 21:50

Albert D. Kallal

42,205
3
34
51

I know how to pass parameter from .NET. What I'm trying to understand is whether it is possible to pass a parameter to a query from another query. – andr111 Jul 10 '14 at 17:37

Pass parameter to a query from another query in Access

2 Answers2

Linked