Uing the In clause in a parameter query

[Guest]

Hello!
I'm trying to run a parameter query and use the "In" clause (e.g., "In
(400,405,500)"). For some reason Access doesn't seem to allow this with
multiple criteria -- it does work with a single number (e.g., "In (400)").
I've tried it with quotation marks around each criteria within the
parentheses as well as no quotation marks.

All help appreciated!

 

[MGFoster]

Hello!
I'm trying to run a parameter query and use the "In" clause (e.g., "In
(400,405,500)"). For some reason Access doesn't seem to allow this with
multiple criteria -- it does work with a single number (e.g., "In (400)").
I've tried it with quotation marks around each criteria within the
parentheses as well as no quotation marks.

All help appreciated!

show the whole query

 

[John Spencer (MVP)]

It would be nice if you could do it this way, however, ...

The parameter doesn't get split up into its components, so In "400,405,500" gets
searched as all ONE value. There are several ways to get around this.

--Build the SQL clause in VBA
--Build just the where clause and apply a filter to the query when calling it
for a report.
--Use the VBA function InStr

The latter would be something like this.
SELECT ...
FROM ...
WHERE InStr(1,"," & [Your Parameter] & ",","," & [TheField] & ",") > 0

If you are doing this in the grid, that would be:

Field: InTheString: InStr(1,"," & [Your Parameter] & ",","," & [TheField] & ",")
Criteria: >0

 

[Guest]

Something like the following:

SELECT Jiffy.Code, Jiffy.Description, Jiffy.CatalogNumber
FROM Jiffy WHERE (Jiffy.SectionCode in [TheParameterGoesHere])
ORDER BY Jiffy.Code, Jiffy.CatalogNumber;

 

[MGFoster]

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

John Spencer answered your question. Here it is again:

== begin quote ==
It would be nice if you could do it this way, however, ...

The parameter doesn't get split up into its components, so In
"400,405,500" gets
searched as all ONE value. There are several ways to get around this.

- --Build the SQL clause in VBA
- --Build just the where clause and apply a filter to the query when
calling it for a report.
- --Use the VBA function InStr

The latter would be something like this.
SELECT ...
FROM ...
WHERE InStr(1,"," & [Your Parameter] & ",","," & [TheField] & ",") > 0

If you are doing this in the grid, that would be:

Field: InTheString: InStr(1,"," & [Your Parameter] & ",","," &
[TheField] & ",")
Criteria: >0
== end quote ==

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

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

iQA/AwUBQkW974echKqOuFEgEQK5owCfbwjZlPTBuAPC/+uVY54l0KYJWtAAoMcI
seHcYdl6Co2cneQjRhB2dLjV
=qFiB
-----END PGP SIGNATURE-----

Something like the following:

SELECT Jiffy.Code, Jiffy.Description, Jiffy.CatalogNumber
FROM Jiffy WHERE (Jiffy.SectionCode in [TheParameterGoesHere])
ORDER BY Jiffy.Code, Jiffy.CatalogNumber;

:

show the whole query