Dynamic Parameter In Query

[orna]

Hi

A query criteria is dinamic.
It looks like: In ("122005","012006")
I tried to insert a function that returns the whole sentence
or only what inside the brackets, and both ways are not working.
If i take the results of the function and place it in the
criteria, the query work ok.
Is there a way to do it?

TIA

 

[Remou]

Do you mean something like:
[tt]Dim qdf As QueryDef

strQry = "qryQuery"

Set qdf = CurrentDb.QueryDefs(strQry)

strIN = "'122005','012006'"
strSQL = "SELECT tblTable.ID From tblTable " _
& "WHERE tblTable.ID In ( " & strIN & ")"

qdf.sql = strSQL
DoCmd.OpenQuery strQry[/tt]

 

[orna]

Thanks for quick answer!

but i need it in the query builder.
Have crosstab query that uses this query.

 

[Remou]

I do not quite understand what you wish to do. You may need to add parameters to your crosstab.

 

[orna]

I need the parameter in a regular query.
The crosstab is based on that query.

 

[Golom]

You'll need to do it Remou's way. The query designer won't accept a parameter as the contents of an IN clause and treat it as a comma-separated list of values. It will be treated as a single string that your field value must match.

 

[orna]

Thanks Remou & Golom. I'll give it a try tomorrow morning.

 

[lespaul]

Also check out:

FAQ701-4696
FAQ701-1927
FAQ701-4432

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[orna]

Thanks Leslie!

I insert the parameter in the crosstab query and build it within the code.