Problem with a Function as criteria in a query 7

[relewis]

I have made this code which collects the value of the bound column of a multiselect list box and creates a string that I want to use in the criteria field for a query:


Public Function PrintExemptionVendorRows()
Dim ctlList As Control, varItem As Variant, strCollect As String

' Return Control object variable pointing to list box.
Set ctlList = Forms![frmPrintExemptionSummary]![List8]
strCollect = Empty

' Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected

' Create a list of the values of the bound columns of the listbox selection.
If strCollect = Empty Then
strCollect = ctlList.ItemData(varItem)
Else: strCollect = strCollect & " or " & ctlList.ItemData(varItem)
End If

' Debug.Print strCollect
Next varItem

PrintExemptionVendorRows = strCollect
' Debug.Print PrintExemptionVendorRows

End Function

What I end up with is a string looking like this: 11 or 14 or 16 or 88 or 92

I put the statement PrintExemptionVendorRows() in the criteria field of the query I want to run with the above statement as criteria. However, when I run the query I get no results, as if the query doesn't see the function as a query statement, but perhaps as a text string; as if "11 or 14 or 16 or 88 or 92" is within quotes, which WOULD give me no results.

Can anyone help me figure out what I'm doing wrong? Thanks

Robin

 

[scriverb]

Change your Where statement to the following:

WHERE InStr(1,TargetStr(),[tblProjects].[Project])>0

You only need to evealuate if the field string value[Project] is within the Target string.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[wvandenberg]

Thank you, Thank you, Thank you.

 

[wvandenberg]

Is it possible to add "ALL" to the top of the listbox. I have read about using a union query but was not able to get it to work. My listbox looks like this:

RowSourceType: Table/Query
RowSource:
SELECT tblProjects.Project FROM tblProjects WHERE (((tblProjects.SiteID)=[Forms]![frmMaster]![ID])) ORDER BY tblProjects.Year DESC , tblProjects.DateEntered DESC
ColumnCount: 1

Wendy

 

[scriverb]

Give this a try:

SELECT  tblProjects.Project as Project, SortOrder as 2 FROM tblProjects WHERE (((tblProjects.SiteID)=[Forms]![frmMaster]![ID])) ORDER BY  SortOrder, tblProjects.Year DESC , tblProjects.DateEntered DESC
UNION 
SELECT  "All" as Project, SortOrder as 1 FROM tblProjects GROUP BY "All", 1;

I believe this will put "All" at the very beginning and then all of your selected projects in the sort order that you already have indicated.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

Sorry about that, it is late and I am beat. Here is a correction. Use this SQL and save as a seperate saved query. Then create another query taking in this query as the input recordset and sort on your fields with SortOrder being the primary.

Query1:

SELECT  tblProjects.Project as Project, 2 as SortOrder  FROM tblProjects WHERE (((tblProjects.SiteID)=[Forms]![frmMaster]![ID])) ORDER BY  SortOrder, tblProjects.Year DESC , tblProjects.DateEntered DESC
UNION 
SELECT  "All" as Project, 1 as SortOrder FROM tblProjects GROUP BY "All", 1;

Query2:

SELECT Query1.Project, Query1.SortOrder
FROM Query1
ORDER BY Query1.SortOrder, Query1.Project

Use Query2 as the RowSource for the listbox.



[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[wvandenberg]

Thanks. I'll give it a try in the morning, I'm beat too!

Wendy

 

[scriverb]

Let's make another adjustment to the code I gave you. Clearer head this morning. Must have been dreaming about this last night.

Query1:

SELECT  tblProjects.Project as Project, 2 as SortOrder, tblProjects.Year as Year, tblProjects.DateEntered as DateEntered FROM tblProjects WHERE (((tblProjects.SiteID)=[Forms]![frmMaster]![ID])) 
UNION 
SELECT  "All" as Project, 1 as SortOrder, 1 as Year, #01/01/1910# as DateEntered FROM tblProjects GROUP BY "All";

Query2:

SELECT Query1.Project 
FROM Query1
ORDER BY Query1.SortOrder, Query1.Year DESC, Query1.DateEntered DESC;

I think this should cover it for you. The sorting must take place is the last query for the it to stick. The SortOrder field puts the only record with a 1 at the top which is "ALL" and the rest are all 2's. So, when you run the second query it will sort them in the SortOrder first and then by the original sort that you wanted. Just use this second SQL for the RowSource of the ListBox and you will have all the selections in the right order. You will have to put code in the AfterUpdate of the listbox to identify the selection ALL and modify you process from there.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]