Queries with multiple parameter values

[Jenny]

Dir All,
ich want to parameterise the following query.
SELECT Kunden.ID, Kunden.Firma, Kunden.Nachname
FROM Kunden
WHERE (((Kunden.ID) In (1,2,3,4)));

I try to replace the expression (((Kunden.ID) In (1,2,3,4)))
with (((Kunden.ID) In (ParamKundenIDs)))
But when i replace it in VBA like the following Example , it does't work.

For Each varItm In lstFldKdeFirma.ItemsSelected
VarKundeID = CStr(lstFldKdeFirma.ItemData(varItm)) & ","
Next varItm
VarKundeID = Left(VarKundeID, Len(VarKundeID) - 1) & ")"
Set qry = CurrentDb.QueryDefs("Abfrage1")
qry.Parameters![ParamKundeIDs] = VarKundeID
qry.Execute

I will appreciate any Help.
Thank you very much in Advance.

 

[Tom van Stiphout]

On Mon, 6 Apr 2009 06:24:14 -0700, Jenny

You can't parameterize an IN clause. What you can do is create the sql
statement in-line, or change the querydef programmatically (qry.SQL
property).

-Tom.
Microsoft Access MVP

 

[Ken Sheridan]

A parameter cannot be used with the IN operator. You'll find two
alternatives at:


http://support.microsoft.com/kb/100131/en-us


If you use the second alternative, using the InParam and GetToken functions,
which caters better for Nulls than the first, the query would be:

SELECT ID, Firma, Nachname
FROM Kunden
WHERE InParam(ID, ParamKundenIDs);

To open the query in VBA, however, you can build the SQL statement in code
and open it. First add the following function to the database:

Public Sub OpenTempQuery(strSQL As String)

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strQdf As String
Static n As Integer

Set dbs = CurrentDb

n = n + 1
strQdf = "qdfTemp" & n

' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(strQdf, strSQL)

' evaluate QueryDef object's parameters if any
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm

' open temporary query
DoCmd.OpenQuery strQdf

' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name

End Sub

Then use the following code to build the SQL statement and call the function

Const conMESSASE = "No items selected."
Dim strSQL As String, strKundeID As String
Dim varitem as Variant
Dim ctrl As Control

Set ctrl = lstFldKdeFirma

' first make sure items have been selected
If ctrl.ItemsSelected.Count > 0 Then
' build value list of KundeID values
For Each varItm In ctrl.ItemsSelected
strKundeID = strKundeID & "," & ctrl.ItemData(varItm)
Next varItm

'remove leading comma
strKundeID = Mid(strKundeID ,2)

strSQL = _
"SELECT ID, Firma, Nachname " & _
"FROM Kunden " & _
"WHERE ID IN(" & strKundeID & ")"

' call function to open query
OpenTempQuery strSQL
Else
Msg conMESSAGE, vbExclamation, "Warning"
End If

Opening a query in datasheet view like this is a rather ugly way of doing
thing's however. A better alternative is to open a form or report to display
the results. For a form you'd replace the following:

' call function to open query
OpenTempQuery strSQL

with:

' open form
DoCmd.OpenForm, "YourForm", _
OpenArgs:=strSQL

For a report, with:

' open report in print preview
DoCmd.OpenReport, "YourForm", _
View:=acViewPreview, _
OpenArgs:=strSQL

In the open event of the form or report set its RecordSource property with:

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If

Ken Sheridan
Stafford, England

Dir All,
ich want to parameterise the following query.
SELECT Kunden.ID, Kunden.Firma, Kunden.Nachname
FROM Kunden
WHERE (((Kunden.ID) In (1,2,3,4)));

I try to replace the expression (((Kunden.ID) In (1,2,3,4)))
with (((Kunden.ID) In (ParamKundenIDs)))
But when i replace it in VBA like the following Example , it does't work.

For Each varItm In lstFldKdeFirma.ItemsSelected
VarKundeID = CStr(lstFldKdeFirma.ItemData(varItm)) & ","
Next varItm
VarKundeID = Left(VarKundeID, Len(VarKundeID) - 1) & ")"
Set qry = CurrentDb.QueryDefs("Abfrage1")
qry.Parameters![ParamKundeIDs] = VarKundeID
qry.Execute

I will appreciate any Help.
Thank you very much in Advance.