Function in Query Criteria

[KernelK]

Quick question that I know someone here can help me with. I am using a function in a query's criteria to filter the query data. It is a simple function that simply returns a global variable. The global variable is set by a form where the criteria is specified (a multi select list box with agent names). This form goes through the list box finding all selected entries and creates the criteria string that is then passed to the query through the function. Oh, and the table field data type is text. I have tried every variation of syntax for this criteria string, but cannot create one that will use multiple criteria. I have tried, strCriteria =
"= '" & name1 & "' OR '" & name2 & "' OR '" & name3 & "'"
"'" & name1 & "' OR '" & name2 & "' OR '" & name3 & "'"
name1 & " OR " & name2 & " OR " & name3

I have also tried to switch my double/single quotes around and using chr(34) for double quotes within the string.

The only time I can make the search criteria work is when I have a single selection and the format is without quotes ie strCriteria = name1

What is the proper way of writing the string to pass into the criteria field via my function? Is it possible to specify multiple criteria in this way?

 

[Pat Hartman]

When combining several values with OR, you need to repeat the field name.

Where fld1 = "vala" or fld1 = "valb" or fld1 = "valc", etc.

The most compact solution when stringing together a list of values is to use the IN() syntax.

Where fld1 In("vala", "valb", "valc", "vald", ...)

Surround the values with quotes if fld1 is a string.

 

[KernelK]

Perhaps more elaboration is required...

It will not work even for 1 single criteria if I use the format fld1 = "vala".
What I have is a query set up to pull records from a single table. In the design of this query, I have put the function: Get_Global("TrackOwner") into the criteria field for one of my tables fields. This function returns a global variable string that is set by a form with a listbox of available criteria. If said form sets the variable = "fld1 = 'value'", so that the function places the string "fld1 = 'value'" into the criteria for this field, the query returns no records. If however I set the variable = "value" , it will work and return my results restricted to this single criteria. What I can't figure out is how to set this string variable to restrict based on multiple criteria. Perhaps I am going about it in the wrong way entirely.
P.S. I also tried to string mutliple values together repeating the field name as suggested, but with no results returned, as is the case if I specify the field name at all in the string.

 

[KernelK]

Even Stranger.....

What is even stranger is that if I type my string as you suggested directly into the criteria field of the query, it works (ie "[Field] = 'vala' OR [Field] = 'valb'"). But when I place the function back into the criteria field and use this function to return the exact same string into the field, then my query returns no results. Am I not supposed to pass criteria to the query at run time in this way?

 

[gemma-the-husky]

in a string though you would need "[fieldname] = " & chr(34) & "vala" & chr(34), wouldn't you. Perhaps thats why the string parameter isnt working

 

[KernelK]

I tried to use chr(34) as well, and it makes no difference to Access. I just can't figure out why my criteria string works when I manually type it into the query and run it. But, it does not work when I use a function to pass the exact same string.

Addendum to previous post "Even Stranger":
When I type: [Field] = 'vala' OR [Field] = 'valb' into the criteria and save the query; Access removes the field names and simply keeps the criteria as 'vala' OR 'valb' . Unfortunately when I pass this through the function it fails as well.

 

[grnzbra]

This sounds like something I tried to do a few years ago and couldn't make work.

You might want to try some kind of code in whatever is being driven by the query which would declare a query def, set it equal to the query in question and then use the function to create the WHERE clause and use

qdf.SQL = Query'sSQLStatement & WHEREclause

what does the output of the function look like?

 

[pdx_man]

Re-creating the SQL from the form is a good idea using QueryDefs and updating the SQL. Depending on the values you will be having, another option is to create a calculated field and query against it. In my example I have created a table and put in values a, b, c, d, e, f, g, h. Here is my query:

SELECT TestTable.Field1
FROM TestTable
WHERE (((GetCrit([Field1]))=True));

Here is the GetCrit function:

Public Function GetCrit(TheVal As String) As Boolean
Dim PosVals As String
PosVals = "'a','b','c','d'"

    GetCrit = False
    Select Case InStr(PosVals, TheVal)
         Case Is > 0:
            GetCrit = True
    End Select
End Function

This is very simpliefied, but you may be able to modify it (get rid of the Instr function) to get better results. ie criteria of Cowboy would return a positve hit for Cow.

but if your putting in Invoices or something like that, then this should work.

 

[KernelK]

You might want to try some kind of code in whatever is being driven by the query which would declare a query def, set it equal to the query in question and then use the function to create the WHERE clause and use....

I have seen this as being a possible solution, but I've avoided putting a reference to DAO in my project thus far, using ADODB and ADOX exclusivley, and I would prefer to keep it that way, but if I have to I may go this route.

what does the output of the function look like?

My function simply outputs a string that should be the proper criteria string for my query built from selections in a listbox. To clarify exactly what I'm going for, I have a table with outbound call records, each record in this table has an Owner field which stores the username of the agent who created the record. I have a form with a multi-select listbox that lists all of the agents who use the database. I want to use the selections from this listbox as the criteria for this query, so that it displays only the records of those agents who were selected. So my function Get_Global("TrackOwner") returns a string that is a concatenation of the users in the list. I formatted the string to look exactly like the criteria that I can enter directly into the query design view to get it to work, and I have tried all of the following different combinations as well (the top two work if i type them into design view, but none work from code):
"[Owner] = 'agent1' OR [Owner] = 'agent2' OR [Owner] = 'agent3'"
"'agent1' OR 'agent2' OR 'agent3'"
"= 'agent1' OR 'agent2' OR 'agent3'"
"[Owner] = 'agent1' OR 'agent2' OR 'agent3'"

There are probably others I tried, but these are the ones that made the most sense, and as I said, the top two work just fine if I type them into the criteria field instead of having the function return the string there. I have also tried swapping my double/single quotes, and using chr(34) when constructing the string, but it makes no difference. The most unnerving thing is that I can do a debug.print to output my string that is being sent to my query (works for the top two examples above anyway), copy it, and paste it into the query and it works.

pdx_man: Very interesting approach. It will take some modification, but I may be able to use this approach. You know, if Access decides to be co-operative today. What do you think, remove the Select case, and instead test the function input (field1) directly against an array of criteria that I create from my listbox? That should only return those records that match my criteria without being bothered with creating a "proper" criteria statement, correct?

 

[gemma-the-husky]

I've had a play with this and can't get it working. I presume thinking about that including sql words like in or and etc in the criteria line of a query, creates some sort of token with the query compiler that cannot be emulated by a string. All that happens, however complex the string is, is that the whole string is taken as a criteria statement.

Would that make sense? - When you type in a SQL ketword its immediately capitalized, so there's something going on in there.

 

[gemma-the-husky]

looking at querydefs collection in Access help

Sub NewQuery()
Dim qdf As QueryDef
Dim strSQL As String

' Create SQL string to select employees hired on or after 1-1-94.

strSQL = "SELECT * FROM Employees WHERE HireDate >= #1-1-94#;"
' Create new QueryDef object.
Set qdf = currentdb.CreateQueryDef("RecentHires", strSQL)
' Open query in Datasheet view.
DoCmd.OpenQuery qdf.Name
Set dbs = Nothing
End Sub

I suspect if you actually constructed a query def as in the above example then you could set the where clause equal to your function, as NOW it just a string.

Perhaps you could cut the sql of your query design without any parameters and try it in here, up to the where clause.

 

[pdx_man]

Yes, that would work. I used the Select Case to illustrate that you can optionally test for lots of things.

BTW, the reason the function does not work just returning the string is what the query sees coming back is " 'a', 'b', 'c' ". So if your criteria is:

IN (GetCrit([blah]))

what the query sees upon evaluation is:

IN (" 'a', 'b', 'c' ")

and since you don't have anything that is equal to what is contained between the double quotes, you get nothing. Try it with just one item, it will probably work.