using a function as a query criteria 2

[sub5]

Hi all

I have a query which uses a function to create a criteria. If I only select one item from my listbox then the query works OK applying the criteria returned by the function crit. However when I select more than one, the query returns nothing. When I print crit in the dubug window it returns a correct string eg something like: 10 or 11 or 14. If I cut and paste that into the query, it works. Is my problem something to do with syntax, speech marks perhaps?

I have this declaration in frm1_0Contractcreate module:
Public clnEngIDs As New Collection

I have this code in a class module clsEngID:
Public mstrengID As String

I have this code in frm1_0Contractcreate module under cmd1_click:
(Nb lstengIDselect is a multiselect listbox)
Dim frm as form, ctl as control, varItm as variant
Set frm = Forms!frm1_0Contractcreate
Set ctl = frm!lstengIDselect

For Each varItm In ctl.ItemsSelected
Dim inst As New clsEngID
inst.mstrengID = ctl.ItemData(varItm)
clnEngIDs.Add inst
Set inst = Nothing
Next varItm

I have this code in module1:
Public Function crit() As String
Dim inst As Variant, strSQL As String
For Each inst In Forms!frm1_0Contractcreate.clnEngIDs
strSQL = strSQL & " Or " & inst.mstrengID
Next
strSQL = Right(strSQL, Len(strSQL) - 4)
crit = strSQL
End Function

I have this SQL in a Query1:
SELECT [Tp-Engmaster].EngID, [Tp-Engmaster].GenericEngine
FROM [Tp-Engmaster]
WHERE ((([Tp-Engmaster].EngID)=crit()));

 

[TheAceMan1]

How are ya sub5 . . .

Is it your intent to [blue]OR[/blue] the selections equivalent to . . .

[blue]WHERE (([Tp-Engmaster].EngID=12) OR ([Tp-Engmaster].EngID=20) OR ([Tp-Engmaster].EngID=12));[/blue]

See Ya! . . . . . .

 

[sub5]

Hi AceMan, top of the mornin' to ya,
yes that is my intention

 

[sub5]

....just replaced

Public Function crit() As String
Dim inst As Variant, strSQL As String
For Each inst In Forms!frm1_0Contractcreate.clnEngIDs
strSQL = strSQL & " OR " & inst.mstrengID
Next
strSQL = Right(strSQL, Len(strSQL) - 4)
crit = strSQL
End Function

with

Public Function crit() As String
Dim inst As Variant, strSQL As String
For Each inst In Forms!frm1_0Contractcreate.clnEngIDs
strSQL = strSQL & " OR ([Tp-Engmaster].EngID=" & inst.mstrengID
Next
strSQL = Right(strSQL, Len(strSQL) - 4)
crit = strSQL
End Function

But still same problem...

 

[PHV]

You may try the following.
Public Function crit() As String
Dim inst As Variant, strSQL As String
strSQL = ","
For Each inst In Forms!frm1_0Contractcreate.clnEngIDs
strSQL = strSQL & inst.mstrengID & ","
Next
crit = strSQL
End Function

And your Query:
SELECT EngID, GenericEngine
FROM [Tp-Engmaster]
WHERE InStr(crit(), ',' & [EngID] & ',')>0;

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

 

[sub5]

Thanks PHV, that worked a treat!
But how?
Instr is checking there is a value, right?
Say I choose two engines then crit returns ,10,11,
and what is happening in the query?

 

[PHV]

If crit() returns ",10,11," and EngID equal, say, 11 for the current row then the WHERE clause evaluates the following expression:
InStr(",10,11,", ",11,")


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

 

[TheAceMan1]

sub5 . . .

[ol][li]PHV is packing a string.[/li]
[li]InStr returns an index to the position of the 1st character of the search string . . . if found! Zero Otherwise.[/li]
[li]The search string turns out to be (assuming [EngID]=11)

[blue]  ",11,"[/blue]

.
While the function returns

[blue]   ",10,11,"[/blue]

Giving a returned index by InStr of 4[/li][/ol]

See Ya! . . . . . .

 

[sub5]

That's nifty! cheers guys.