Parameter Queries, WHERE...IN.... clauses & VBA - Do they blend?

[Guest]

SELECT VBELN, POSNR, MATNR, ARKTX, KWMENG, WERKS, ERDAT
FROM VBAP
WHERE KWMENG In (1,2,4);

If I use the query above, using the WHERE...IN.... syntax, it works fine,
returning the same result as if I had typed:
WHERE KWMENG = 1 OR KWMENG = 2 OR KWMENG = 4

Is it possible to use a parameter query, in conjunction with the
WHERE....IN.... syntax, to allow for multiple numbers to be input? I have
tried the following, with varitaions of the data type (integer, text, etc),
but it doesn't work:

PARAMETERS [Qty] Text ( 255 );
SELECT VBELN, POSNR, MATNR, ARKTX, KWMENG, WERKS, ERDAT
FROM VBAP
WHERE KWMENG IN ([Qty]);

or

PARAMETERS [Qty] Short;
SELECT VBELN, POSNR, MATNR, ARKTX, KWMENG, WERKS, ERDAT
FROM VBAP
WHERE KWMENG In ([Qty]);

The reason I am trying to figure this out is that when altering the criteria
on the fly in VBA, I would rather just modify the
QueryDef.Parameters.Parameter property than have to completely reconstruct
the query using the QueryDef.SQL property.

Thanks!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You cannot indicate a comma-delimited parameter for the IN () clause.
The parameter will be read as one string. E.g.: "a,b,c,d" would be
like this in the IN () clause:

KWMENG IN ('a,b,c,d')

IOW, the string w/ comma-separated items will be passed as one item to
the query instead of the 4 items you want.

You'll have to create the comma-delimited string in VBA & then concat
that string into the SQL string. E.g.:

strWhere = "WHERE KWMENG In ("
for i = 1 to 4
strWhere = strWhere & i & ","
next i
' clean off trailing comma & add the closing paren.
strWhere = left$(strWhere,len(strWhere)-1) & ")"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQZ7EPIechKqOuFEgEQKEBwCdE9mEmt0HYSnydbcGoBTfrV9QvsgAoOk2
4UQdSEY+vWtulwO+WwyU3KCd
=Dizl
-----END PGP SIGNATURE-----