Select statement nightmare

[Psycho_NZ]

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

[Mongo]

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

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

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

[Ecniv]

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

[Psycho_NZ]

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)

[Psycho_NZ]

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

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