Using a function as a criteris in a query

[Sparky]

Not sure if this belongs in programming or queries, but....

The following query runs exactly as I want it to.

The query:


SELECT Query9.ID, Query9.BusinessGroup, IIf(forms!TestQ!Text0="All Business Groups","1",0)+IIf(Forms!TestQ!Text0="HR",2,0)+IIf (Forms!TestQ!Text0="Manufacturing",3,0) AS AA, Query9.FunctionID, Query9.FunctionName, Query9.FunctionType, Query9.XX, Query9.BB, IIf([BB]=" 'HR' Or 'Manufacturing'","Equal","Not") AS c
FROM Query9
WHERE (((Query9.BusinessGroup)='HR' Or (Query9.BusinessGroup)='Manufacturing'));

The Criteria in the criteria row of the query design grid:

'HR' Or 'Manufacturing'

Then, I have a function that cycles through a listbox and gets essentially the same string. The function is called GetCriteria(). I have a debug.print statement in the function to see what the final string is. Final string is:

'HR' Or 'Manufacturing'

When I use the function GetCriteria() in the criteria row for field BusinessGroup, I get no records. Following is the SQL for when the GetCriteria() function is used.

SELECT Query9.ID, Query9.BusinessGroup, IIf(forms!TestQ!Text0="All Business Groups","1",0)+IIf(Forms!TestQ!Text0="HR",2,0)+IIf (Forms!TestQ!Text0="Manufacturing",3,0) AS AA, Query9.FunctionID, Query9.FunctionName, Query9.FunctionType, Query9.XX, Query9.BB, IIf([BB]=" 'HR' Or 'Manufacturing'","Equal","Not") AS c
FROM Query9
WHERE (((Query9.BusinessGroup)=GetCriteria()));

Can someone suggest why I don’t get any records with the second Select statement and I get all the records I expect with the first Select statement?

[June7]

I tried your idea and it won't work. I think would have to modify the query definition with QueryDef

I don't like to use dynamic parameters in queries. I prefer to use WHERE CONDITION of DoCmd.OpenForm (or OpenReport).

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

[Sparky]

I tried your idea and it won't work. I think would have to modify the query definition with QueryDef

I don't like to use dynamic parameters in queries. I prefer to use WHERE CONDITION of DoCmd.OpenForm (or OpenReport).

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

Good idea, I'll try this.