Looking for advise with query parameters.

[mick_green]

Hi,

I'm looking for assistance with queries / VBA coding - not sure which discussion this best belongs in really...

For background, I have a number of queries which run in succession as they would be too complicated to have as one query or given the limitations of Access which would not allow it. So, I am "stuck" with a number queries but this question is really to do with calling them rather than what each one actually does.

The first question is to do with passing a parameter to the where clause of the first query, which looks like this:

SELECT A1,B1,C1,D2
FROM tblSelection
WHERE (Selection In ([@Param2]))

and I am calling it like this:

Private Sub ParamTest()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qr As DAO.QueryDef
    
    Set db = CurrentDb
    Set qr = db.QueryDefs("ParamTest")
    
    qr.Parameters("@Param2").Value = "'10A','10B','10C'"
    
    Set rs = qr.OpenRecordset
    
    Do While Not rs.EOF
        Debug.Print rs.Fields(1).Value
        rs.MoveNext
    Loop
    
    Debug.Print
    
End Sub

the problem is that no results are returned. I have a suspicion that this is to do with qr.Parameters("@Param2").Value = "'10A','10B','10C'" because if I change it to qr.Parameters("@Param2").Value = "'10A'", I get results back, so I think it is to do with the list of strings???

Does anyone know a solution to this as it would be useful to know for other queries which can be run on their own?

Now I could forgo having them as queries, which is useful as I can call with different parameters everytime I need different data, and code them in VBA but my next question is if I did that, how do I run a query on the recordset of a previous query? So, if I need to run 3 queries, I can run the first which gives me a recordset, but the second query needs to use the results of the first query...

 

[theDBguy]

Hi. Welcome to AWF!

I am guessing the problem here is that the code is actually passing the entire string ("'10A','10B','10C'") as a single string, instead of individual strings. I think that has to do with how the query interprets it. You may have to try another approach to using the In() operator.

See if this article gives you any ideas.

Create a Parameter Query to accept multiple values

Parameter queries are a quick way to create dynamic queries where the result changes depending on user input. If you are not familiar with p...

thedbguy.blogspot.com

 

[CJ_London]

The jet/ace db is not as sophisticated as sql server - if you want that level then suggest use sql server express or similar.

you would need to include the parameters in the query, although your comments imply it is not required.

PARAMETERS [@Param1] Text ( 255 );
SELECT A1,B1,C1,D2
FROM tblSelection
WHERE (Selection In ([@Param2]))

However you can't create the IN criteria in in this way, but as you have found out, can use it for =, <> and Like

you could try with a modified query

SELECT A1,B1,C1,D2
FROM tblSelection

param="'10A','10B','10C'"
qr.sql=qr.sql & "WHERE Selection In (" & param & ")"

with regards your second question using the above method, you have modified the query sql so your second recordset would be based on calling that query

 

[mick_green]

Thanks all,
I'll give these suggestions a go and see which one fits in best