Looking for advise with query parameters. (1 Viewer)

mick_green

New member
Local time
Today, 13:57
Joined
Oct 8, 2021
Messages
2
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:
Code:
SELECT A1,B1,C1,D2
FROM tblSelection
WHERE (Selection In ([@Param2]))

and I am calling it like this:
Code:
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

I’m here to help
Staff member
Local time
Today, 06:57
Joined
Oct 29, 2018
Messages
21,357
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.

 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:57
Joined
Feb 19, 2013
Messages
16,553
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.

Code:
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

Code:
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

New member
Local time
Today, 13:57
Joined
Oct 8, 2021
Messages
2
Thanks all,
I'll give these suggestions a go and see which one fits in best :)
 

Users who are viewing this thread

Top Bottom