Using a Query parameter in an "In()"-Clause

  • Thread starter Thread starter Hyko
  • Start date Start date
H

Hyko

Hello!

I want to create a querey in access where i have the following criteria:

SELECT * FROM tblContracts WHERE suppliernum IN ([qSupplierNumList]);

This means on executing the query I want to be asked to enter a list of
numbers. Somehow this does not work. Is there any possibility to make this
work or is it not possible with variable parameters like [qSupplierNumList]?

The problem is that i do not know how many numbers will be used in advance
and I want to pass a array or list to this stored query later from asp.

Thanks for your replys in advance and sorry for my bad English.

Hyko
 
Jim,

thank you for your suggestion. I think I did not formulate my problem
correctly and maybe I misguided you a little bit with the q in
qSupplierNumlist. This is not a query, but a query parameter.

I want to pass a string or array or whatever as value of the "variable"
[qSupplierNumList] to the query in that way, that the IN() command can
handle this as list of values.

I want to write "...suppliernum IN ([qSupplierNumList]);"
And give qSupplierNumList a value with help of the access input box or later
with help of the parameter definition in vb/asp.
I first thought it would be enough to give the value e.g. 10;20;13;16, but
the In()-function will interpret this as one value and of course find no
entries.

I am now looking for an idea how I can persuade access to handle this
parameter as if I had typed a list manually.

Thanks again and thanks in advance for further hints.

Heiko


Am 24.03.2004 17:41 Uhr schrieb "Jim Richards":
 
Heiko

Your IN list is supposed to be comma-delimited, not semi-colon. (10;20;13;16) should be (10,20,13,16)

Hope this helps

Ji
 
Jim,

I forgot to mention that I am using the German version of Access where the
list-delimiter is the semi-colon and the comma is the decimal-sign. Anyway,
I tried both and IN always seems to interpret the value as one string.
My next idea was to find a split(string, delimiter)-like function. But I did
not find one. Then I wrote one in visual basic using the split-command there
but IN does not except the returned array as well.

Thankful and clueless greetings,

Heiko

Am 24.03.2004 23:16 Uhr schrieb "Jim Richards":
 
Heiko

Sorry, my German SQL is a little rusty. My only suggestion is the old "make sure you have the lastest service pack and patches" line you hear so often

Sorry I couldn't be more helpful

Jim
 
Back
Top