passing string variable into criterion

[Milothicus]

I'm building a string of criteria with a public function, but can't get the query to recognize it. i've stepped through the code, and it should work, but i can't figure out the syntax to get my query to recognize the criterion.

well, i may as well describe the whole situation.
i have a field in my table that categorizes the records. for search purposes, if the value is from 0 to 6, it's current, if it's 7 it's won, 8 it's lost. i have checkboxes on my search page to include or exclude each of the 3 categories. here's the code in my function:

Public Function fEnqStg(won, lost, current)
    Dim str As String
    str = ""
    If current Then
        str = str + "BETWEEN 0 AND 6"
    End If
    If won Then
        str = str + " OR 7"
    End If
    If lost Then
        str = str + " OR 8"
    End If
    fEnqStg = str
End Function

where won, lost, and current are passed through the function from the form. this works fine too. i've tested it. it's just the passing of the string to the criterion field in the query design that's not working.

edit: here's what's in the criterion box:

fEnqStg([Forms]![frmOverall]![optOrdrEB],[Forms]![frmOverall]![optHistEB],[Forms]![frmOverall]![optCurrentEB])

 

[sfreeman@co.mer]

Not very elegant, but this works. Forget the function...

SELECT
tblSearchVal.SearchVal

FROM
tblSearchVal

WHERE
(((tblSearchVal.SearchVal)=IIf([Forms]![frmOverall]![optOrdrEB],0))) OR (((tblSearchVal.SearchVal)=IIf([Forms]![frmOverall]![optOrdrEB],1))) OR (((tblSearchVal.SearchVal)=IIf([Forms]![frmOverall]![optOrdrEB],2))) OR (((tblSearchVal.SearchVal)=IIf([Forms]![frmOverall]![optOrdrEB],3))) OR (((tblSearchVal.SearchVal)=IIf([Forms]![frmOverall]![optOrdrEB],4))) OR (((tblSearchVal.SearchVal)=IIf([Forms]![frmOverall]![optOrdrEB],5))) OR (((tblSearchVal.SearchVal)=IIf([Forms]![frmOverall]![optOrdrEB],6))) OR (((tblSearchVal.SearchVal)=IIf([Forms]![frmOverall]![optHistEB],7))) OR (((tblSearchVal.SearchVal)=IIf([Forms]![frmOverall]![optCurrentEB],8)));

 

[Milothicus]

works great. thanks. apparently, i didn't know how to use the criteria properly, but do now.

 

[Mile-O]

Wouldn't this just do the same thing?

WHERE tblSearchVal.SearchVal In (0,1,2,3,4,5,6,7,8)

 

[Milothicus]

no because i have three groups (1,2,3,4,5,6), (7), and (8) that i want to either return or not by check boxes.