Store an operator and replace a string with it

[Ruegen]

I am using a tempvars to filter a query



I want to filter the row source of a listbox using the ID numbers and an OR operator

so 156 OR 156

however tempvars only stores the string and not the operator

I could go through the string and replace a trailing "," with an operator but the replace function won't allow that.

Basically if I can get the query criteria to pick up multiple IDs as well as the operator then I can easily filter the listbox

[June7]

I don't understand the criteria rule.

Why the same parameter twice?

Where are the fields in that criteria?

[Field1] = 156 OR [Field2] = 156

If you want multiple parameters applied to a single field:

[Fieldname] IN (155, 156, 157)

Review http://allenbrowne.com/ser-50.html

Where are the parameters coming from?

[Ruegen]

so if I build/concantinate a string with the tempvar and have the ","

I could put in the criteria of the query ([fieldname] IN (tempvars!tmpIDs)) ?

when the user clicks in the listbox a string is built appending each ID from the ID column of that listbox.

[Ruegen]

Currently I use this

(note I know I could use a standard variable rather than the tempvar but this is on the fly)

Dim strArea As StringstrArea = ""
strArea = "((tblAreas.AreasID)=" & Me.lstAreas.Column(0) & ")"


If TempVars!tmpfiltercatalogues = "" Then
TempVars!tmpfiltercatalogues = strArea
Else
TempVars!tmpfiltercatalogues = TempVars!tmpfiltercatalogues + " OR " & strArea
Debug.Print "Searching " & TempVars!tmpfiltercatalogues
End If


strSQL = "SELECT Count(tblSchools.NewSchoolsID) AS Schools, tblTourOrganiser.MergedNameT AS [Tour Organiser], Count(IIf(IsNull([cataloguelabelprinted]) Or ([cataloguelabelprinted]<DLookUp(""CatalogueDate"",""tblCataloguesTourOrganiser"")),1,Null)) AS [Catalogue Not Sent], Count(IIf(IsNull([cataloguelabelprinted]) Or ([cataloguelabelprinted]<DLookUp(""CatalogueDate"",""tblCataloguesTourOrganiser"")),Null,1)) AS [Catalogue Sent], tblAreas.Area " & vbCrLf & _
"FROM (tblCataloguesTourOrganiser RIGHT JOIN tblTourOrganiser ON tblCataloguesTourOrganiser.CataloguesTourOrganiserID = tblTourOrganiser.CataloguesToCall) RIGHT JOIN (tblAreas INNER JOIN tblSchools ON tblAreas.AreasID = tblSchools.AreaID) ON tblTourOrganiser.TourOrganiserID = tblAreas.TourOrganiserID " & vbCrLf & _
"WHERE ((tblSchools.SchoolTypeID)=2) AND ((tblSchools.Enrollment)>100) AND " & TempVars!tmpfiltercatalogues & " " & vbCrLf & _
"GROUP BY tblTourOrganiser.MergedNameT, tblAreas.Area, tblTourOrganiser.TourOrganiserID " & vbCrLf & _
"ORDER BY tblTourOrganiser.MergedNameT, Count(IIf(IsNull([cataloguelabelprinted]) Or ([cataloguelabelprinted]<DLookUp(""CatalogueDate"",""tblCataloguesTourOrganiser"")),1,Null)) DESC;"


Debug.Print "Below" & vbCrLf & strSQL


Me.List0.RowSource = strSQL
Me.List0.Requery

[June7]

Try it. From what I can remember that will not work as a dynamic parameter in query object but should work in the sql string constructed with VBA.

[Ruegen]

Try it. From what I can remember that will not work as a dynamic parameter in query object but should work in the sql string constructed with VBA.

Well the typed operator OR is within the string and not outside of it when stored in the variable. I would like a method of carrying the operator into stored memory outside of a string (pretty sure I can't) however if there was a function that parses out the " , " and inserts an operator OR outside the string... ie. " & OR & " then I could have function(variable,",", OR ) or something like that. How I see it I can't pass an operator through a function so what I am suggesting won't work either.

[June7]

OR operator is not going to do anything except trigger error if there is no field with each parameter: ... WHERE [Field1] = 156 OR [Field2] = 156 ...

It is possible to use Replace function to replace a comma in a string, but like I said, won't accomplish anything without associated field names.

OR operator does not work to build a set of values like would be used with IN() criteria.

The OR operator can certainly be held in a variable and then that variable used in concatenation to construct an SQL statement.

[Ruegen]

*slaps own head*

sorry june yes you are right - I would need to add the field for each ID (([field]=125) OR ([field]=126))

however that is coding the SQL where - I want to simply insert the selected IDs into the criteria. If you suggest In() will do that...

[Ruegen]

Replace this

Attachment 18471


with this

Attachment 18472

(yes I know field is spelt incorrectly)

[June7]

I don't think you will get what you want with a dynamic parameterized query. I don't even use dynamic parameterized queries.

[Ruegen]

I don't think you will get what you want with a dynamic parameterized query. I don't even use dynamic parameterized queries.

I keep a statistics form where the boss wants to pick from a list of areas to display on another list. This way he can pick say cities 'New York', 'Chicago' & 'Los Angeles' from the first list and they will appear with statistics in the second list. If he wants he can decide what he wants to display in the list. This is good for excluding certain cities.

[Ruegen]

as an example (note - this works and there are no subforms involved).

[June7]

What is the mechanism - dynamic parameterized query?

[Ruegen]

What is the mechanism - dynamic parameterized query?

Currently I reload the rowsource of the listbox with the built (and appended parameters) sql string

Which is slow, I'd rather just requery the listbox with the tempvar as the parameter

Most of what is done can be seen post #4

when you click an area it appends that ID of the area to there tempvar string which is in the sql string (eventually) then it reloads the row source of the other listbox.

[June7]

Still don't think dynamic parameter will work.

Options:

1. present method

2. subforms and set Filter and FilterOn properties