using a select stmt to retun values based on values in an array

[Maggie24]

Hi all,

I have created an array which holds unique product nos of records i have updated in my db.

i now want to select records from my db based on the data (product nos)in the array.

I have:
List42.RowSource = "Select * from tblProducts where Product No in '" & myarray & "'"

but it is giving me a data error.

what is the correct way to select from a table based on values in an array?

PLEASE HELP!

thanks,

Maggie

"Work is the curse of the drinking classes

 

[Crowley16]

firstly, you've got a few syntax errors:

"Select * from tblProducts where [Product No] in '" & myarray & "';"

secondly, you can't do that, as far as I know...

what I do is to use a loop to construct the sql statement...

dim sql as string
sql = "Select * from tblProducts where "

for idx = 0 to arraySize
   sql = sql & "[product no] = '" & myarray(idx) & "' OR "
next idx

 

[Maggie24]

Thank you so much Crowley 16,

That worked perfectly!

Maggie

"Work is the curse of the drinking classes

 

[PHV]

You may also consider this:
"Select * from tblProducts where [Product No] in (" & Join(myarray, ",") & ");"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244