Using IN criteria with parameter queries

[Toxalot]

Access 2003

I want to use a parameter query using IN criteria rather than equal
criteria.

SELECT lngPersonId FROM tblProfilePeople WHERE intRoleId IN
([prmintRoleId]);

If I enter an integer at the prompt it works, but I want to enter more
than one integer. I can do the following in a regular query.

SELECT lngPersonId FROM tblProfilePeople WHERE intRoleId IN (1,2);

I know the above could also be written as
SELECT lngPersonId FROM tblProfilePeople WHERE intRoleId = 1 OR
intRoleId = 2;

But it won't always be just one OR. KWIM?

Can I get this to work in a parameter query?

Jennifer

 

[Stefan Hoffmann]

hi,

SELECT lngPersonId FROM tblProfilePeople WHERE intRoleId IN
([prmintRoleId]);
If I enter an integer at the prompt it works, but I want to enter more
than one integer. I can do the following in a regular query.
Can I get this to work in a parameter query?

Aircode:

--
Option Compare Database
Option Explicit

Public Function QueryIn(AField As Variant, _
AParameters As String _
) As Boolean

'AParameters must be comma-delimited.

Dim Count As Long
Dim Parameters() As String
Dim Result As Boolean

Parameters() = Split(AParameters, ",")
Result = False

For Count = LBound(Parameters) To UBound(Parameters())
Result = (AField = Parameters(Count))
If Result Then
Exit For
End If
Next Count

QueryIn = Result

End Function
--

Use

SELECT lngPersonId
FROM tblProfilePeople
WHERE QueryIn(intRoleId, [QueryForParam]);

mfG
--> stefan <--