Select statement nightmare

Hell, Im trying to select some thing from a table, but i cant quite get it.

this is what happens: the user enters a string of codes like this "a,b,c" for example :) of records they want to get from the database.

so if there was this database:
a 0 1
b 2 5
c 3 4
d 1 7
e 0 0 (3 fields)

I would want the recordset to get the first three because they have the a b and c.

how can you do this?
this is what i have so far if that's any help, it returns the error "No value given for one of more required parameters":

rstResins.Open "SELECT Distinct Code, O2, CO2 FROM Resin WHERE (InStr(1, " & Chr(34) & strresins & Chr(34) & ", Code, vbTextCompare) > 0)"
strresins would be the "a,b,c" and code the database fieldwith the a b c d e in it.

I also was asking the same type of thing earlier:

http://www.vbforums.com/showthread.p...hreadid=175536

Thanks for your time :)
Ang

Check your help files for how to write an SQL statement using IN in your search argument, e.g.:

Code:

---

SELECT
  Code, O2, CO2
FROM Resin
WHERE Code IN ('" & strresins & "')"

---

(Where you build something like this for strresins = "a','b','c" )

Hi,

If the In in the Where section doesn't work, you'll need to split the string of choices and put them into a larger where clause

So "a,b,c" becomes : [code]='a' OR [code]='b' OR [code]='c'

VB provides a function Split (I think) or you can write your own.


Vince

Ah, its getting there, the IN didnt work unfortunately, thanks tho all the same.

So i've split it into its parts like u suggested Ecniv Im going to make a string of code = 'a' Or code = 'b' things. All i need now is a way to tell it when to stop. strResin has an unspecified number of resins on it i.e it could be "a,b,c" or "a,b,c,d,e". I figured id put it in a for loop, what shoud be my stopping number?

I thought maybe splitter.count? but that didnt work. any ideas?

Dim Splitter() As String
Splitter = Split(resins, ",", -1, vbTextCompare)

Ok, i have it selecting them for me, i call another function that returns them formatted in that or fashion, like this:

VB Code:

selectstring = "SELECT Distinct Code, O2, CO2 FROM Resin WHERE " & MakeSQLCond(strresins)
 
Private Function MakeSQLCond(resins As String) As String
Dim i As Integer
Dim temp As String
 
temp = ""
splitter = Split(resins, ",", -1, vbTextCompare)
 
For i = 0 To (UBound(splitter) - 1)
    temp = temp + "Code = '" & splitter(i) & "' OR "
Next i
 
temp = temp + "Code = '" & splitter(UBound(splitter)) & "'"
 
MakeSQLCond = temp
End Function

and its running the .open without any complaints. The problem now is, it cant do operations i need it to perform, i.e .bookmark and .update . Any ideas why?


Thanks for your time :)
Ang