passing parameters to query in VBA

[Michelle]

I have the following function:

Public Function GetRecordset(blnQDef As Boolean, _
strQuery As String, _
Optional strParam As String = "", _
Optional varParamValue As Variant) _
As Recordset


Dim qdf As QueryDef
Dim rst As Recordset

If blnQDef = True Then
If strParam <> "" Then
Set qdf = db.QueryDefs(strQuery)
'***********************************************
'the line below is the one I am struggling with
'***********************************************

qdf.Parameters(strParam) = varParamValue
Set rst = qdf.OpenRecordset(dbOpenDynaset)
Else
Set qdf = db.CreateQueryDef("", strQuery)
Set rst = qdf.OpenRecordset(dbOpenDynaset)
End If
Else
Set rst = db.OpenRecordset(strQuery,
dOpenDynaset)
End If

Set GetRecordset = rst
End Function

I need to call this function where varParamValue can be 3
different values, ie if i was building the query manually
i would have 3 different text values separated by OR.
However, if I call the above function with something like:-


OpenStaffRecordsets "QueryName", "ParameterName", _
"opt1 OR opt2 or opt3"

i don't get any records returned. does anybody know how I
should format the 3rd argument of the OpenStaffRecordsets
function in order for it to find any records which meet
any of the three criteria.

Sorry if this is a bit confusing.

Thanks for any help

Regards
Michelle

 

[Michel Walsh]

Hi,



The SQL statement is to be modified.

You probably have something like:

... WHERE fieldName = param


change it to

... WHERE ("," & param & "," ) LIKE ("*," & FieldName &
",*")



and supply, for parameter, a value like:

"2,4,5" ( no space after the coma)


The query would return the records having the mentioned fieldName value =2,
or =4, or =5.


If the table is large, you may be better using an inner join with a temp
table where you would specify the values, one per record in the temp table.


temp 'table name
param ' field name (primary key or, at least, a constraint UNIQUE)
2
4
5 ' data


------------------------------------------------------
SELECT a.*
FROM myTable As a INNER JOIN temp
ON a.fieldName = temp.param
------------------------------------------------------

is the whole query you need (but you have to "maintain/operate" the temp
table appropriately.



Hoping it may help,
Vanderghast, Access MVP