Parameter query with a variable "IN" clause

[solex]

Hello,

I have created a query which gets it criteria from a function. The function
returns the selection of a combo box in a tool bar. The user can select a
single year from the combo box or choose the item "<ALL>" which represents
all years.

The problem I am having is that the IN clause will not except a string list
of years, although it will except an integer representing a single year. Is
there a way to return a list of integers to the query (I have tried an array
but this does not work.)

Thanks,
Dan

 

[John Viescas]

Is this query bound to a form or report? If so, you'd be better off using
the WhereCondition parameter on OpenForm or OpenReport and remove the
parameter from the query. Your code that performs the filtered open could
still call the function:

Dim strWhere as String

strWhere = "SomeField IN (" & YourFunction() & ")"
DoCmd.OpenForm "frmResults", WhereCondition:=strWhere

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[solex]

John,

Thanks for your response, I will take this under advisment..

Regards,
Dan