Query performance slow

[wvandenberg]

Hi all,

Just wondering if anyone may be able to suggest a way of making this query run faster?

SELECT DISTINCT Month(Nz([StartDate],0)) AS Expr1, MonthName(Month(Nz([StartDate],0))) AS Expr2
FROM tblSamples
WHERE (((InStr("," & [TempVars]![Samples] & ",","," & [pkSampleID] & ","))>0))
ORDER BY Month(Nz([StartDate],0));

pkSampleID is indexed as it is a primary key.

Thanks in advance,
Wendy

 

[Remou]

TempVars is a table that lists a number of IDs, is that correct? In what format are the IDs listed? Can they be listed in such a way as to make a join possible?

http://lessthandot.com

 

[PHV]

a way of making this query run faster
I highly doubt that the posted SQL run without prompting for parameter value.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wvandenberg]

Remou:

TempVars is a new collection for storing global variables (added to Access 2007). From the Help file:

"To refer to a TempVar object in a collection by its ordinal number or by its Name property setting, use the following syntax form:

TempVar![name]"

[TempVars]!{Samples} contains a comma delimited string of integers.

PHV: The SQL actually does run without asking for any user input, if that's what you mean.

 

[PHV]

Sorry, don't know ac2k7 enough ...