Select query using TempVars as criteria 2

[wvandenberg]

Hi all,

I have a select query where I use a TempVar item as the criteria.

SELECT tblSamples.SampleName, tblSamples.pkSampleID
FROM tblSamples
WHERE tblSamples.pkSampleID In ([TempVars]![Samples]);

[TempVars]![Samples] contains a string and tblSamples.pkSampleID is a long integer. Possible values for [TempVars]![Samples] are single integers (eg. 455) or a comma delimited string (eg. 9839,9842,9844,9846,11822,11477,11792). The query works correctly when [TempVars]![Samples] = 467 (or just a single number) but not when it's a comma delimited string. If I enter the criteria directly into the query grid, both scenarios work correctly. I've tried changing the In([TempVars]![Samples]) to just [TempVars]![Samples] as the criteria and also changed the comma delimited string to "9839 Or 9842 Or 9844...". This did not work either.

Can anyone suggest a way to make the [TempVars]![Samples] variable work when there's more than one pkSampleID?

Thanks,
Wendy

 

[dhookom]

Is TempVars a table? If so, I would use code to build the SQL statement since the solution you are attempting won't work. You could try:

SELECT tblSamples.SampleName, tblSamples.pkSampleID
FROM tblSamples, TempVars
WHERE  Instr("," & pkSampleID & ",", "," & Samples & ",")>0;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Duane, I'd use this WHERE clause:

WHERE  Instr("," & Samples & ",", "," & pkSampleID & ",")>0

 

[dhookom]

Oops. Good catch.

Duane
Hook'D on Access
MS Access MVP

 

[wvandenberg]

Sorry, I should have mentioned that I'm using Access 2007 and TempVars is a new built in global variable.

However, I tweaked your suggestion to

SELECT DISTINCT tblSamples.SampleName, tblSamples.pkSampleID
FROM tblSamples
WHERE InStr("," & [TempVars]![Samples] & ",","," & [pkSampleID] & ",")>0;

and it works great. Thanks to both of you for your help.

Wendy