Multiple Selection in Parameter Queries 2

[khwaja]

I would like to create a parameter query to allow user to specfiy criteria seperated by comma in one dialogue box instead of being prsented by several dialogue boxes. For example if I need to pull a report for a number of cost centres, I might want to include all CCs seperated by commas to retrieve relevant pages of the report. This works in the query but not when you wcreate a parameter query for user input. Any ideas if this can be achieved.

Cheers

 

[JoeMiller]

Not with an input box, you'll need to provide a form where the user can input the CC's they want. Then have the form take the string and change the underlying query. Here's a function that changes the SQL code of a query:

[tt]
Function basDefQuery(strQryName As String, strSQL As String)
Dim loqd As QueryDef

Set loqd = CurrentDb.QueryDefs(strQryName)

loqd.SQL = strSQL
loqd.Close

End Function
[/tt]

Usage
?basDefQuery("MyQueryName","SELECT blah FROM blah&quot

Normally you'll build up the second part where I have SELECT and pass it as a variable, but you can put the SQL statement directly in there if you like.

HTH

HTH Joe Miller
joe.miller@flotech.net

 

[khwaja]

Much appreciate your hep Joe.

 

[MichaelRed]

to accomplish the functionallity of your request, I would (often have) set up a set of check boxes for the selection set. These are (obviously) on a form and requires a command to activate the process, I would build the query string from the items on the form (I make the check box captions the SAME as the values for the selection set criteria, and just read the acssociated caption for each box where it is true). In a similar vein, if there can / should be only a single selection, I would use an option group. My purpose is mainly to prevent the erronous generation of a report from typo errors. Having ONLY preset selection criteria works well where the underlying data is static. If it is more dynamic, you can still accomplish this with the added effort of dynamically adding the necessary controls at run time.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over