SQL Select Qustion :)

[Psycho_NZ]

Hiya, here is some SQL:

SELECT Distinct Left$(Code, Len(Code) - 2) As CodeNum, O2, CO2
FROM Resin
WHERE (Right$(Code, 2) = " & cmbGoalTemp.Text & ")
AND (InStr(1, " & strresins & ", CodeNum, vbTextCompare) > 0)

I get this error: "No value given for one of more required parameters"

it's on this line:" AND (InStr(1, " & strresins & ", CodeNum, vbTextCompare) > 0)" im pretty sure because it worked before i added that.

the code field is full of values like this:
ax10
ax20
ax30
bc10
bc20
bc30
g110
g120
g130

and strresins looks like this: "ax,bc"

Can anyone tell me wat's wrong with it?

Thanks for your time
Ang

[peet]

InStr(1, " & strresins & ", CodeNum, vbTextCompare)

looks all wrong... what is it that you want to do with this part. line of code?

[Psycho_NZ]

I only want it to return the records thats code, well codenum, are in the strResin.

so if that database was

ax10
ax20
ax30
bc10
bc20
bc30
g110
g120
g130

and strresin was
"ax,bc"

and cmbtemp was 20

then i want to get the records ax20 and bc20.
does that make sense?

[peet]

not 100% sure that the result of the SQL will be what u want it to, but this

InStr(1, " & strresins & ", CodeNum, vbTextCompare)

should be something like this

InStr(1, strresins, CodeNum, vbTextCompare)

it will return a nonezero val if CodeNum exists in strresins

[Psycho_NZ]

Yes yes, sorry, thats what i have, only it is passed during runtime as a record source, like this :

rstResins.Open "SELECT Distinct Left$(Code, Len(Code) - 2) As CodeNum, O2, CO2 FROM Resin WHERE (Right$(Code, 2) = " & cmbGoalTemp.Text & ") AND (InStr(1, " & "42014213" & ", CodeNum, vbTextCompare) > 0)" , , adOpenKeyset, adLockOptimistic