Use dynamic table list as query criteria

[feathers212]

I have a table called tblPosition that contains a list of cost centers (some cost centers are repeated on this list, so I have also created a crosstab query called qryPositionCostCenter that summarizes the list). I want to use either the table or query list values as criteria within other queries. Basically, I don’t want to hardcode in criteria as my cost center list is dynamic.

Any thoughts on how to do this?

I am using Access 97.

 

[ajetrumpet]

i think what you need to do here is make a list box that lists the two object names that you want in it, and then, concat that value in with your SQL in code. remember though, code can only be used for action queries. if you're doing SELECT queries from these base queries of yours that you've already made, you can probably concat the source name in with that SQL as well, as long as it comes from a list box, or even a function perhaps in the FROM clause.

 

[feathers212]

I was hoping to avoid the use of a list box. I will need to use this dynamic list of criteria in multiple query scenarios.

I understand the concept of concatonating the list into a string, but I'm stuck on how to make that happen.

~Heather

 

[ajetrumpet]

I was hoping to avoid the use of a list box. I will need to use this dynamic list of criteria in multiple query scenarios.

I understand the concept of concatonating the list into a string, but I'm stuck on how to make that happen.

~Heather

we may be able to help you if you upload your database for analysis.

 

[feathers212]

I found my solution:

In the criteria section for the queried field, I enter "In (SELECT [CostCenter] FROM tblPosition)".

Or in SQL view: "WHERE ((([TablelMasterEmployee File].COSTCENTER_NUM) In (SELECT [CostCenter] FROM tblPosition)))"

tblPosition is the table with [CostCenter] being the field containing the dynamic list of criteria I want to use; In SQL, [TablelMasterEmployee File].COSTCENTER_NUM is the selected query field that I wish to filter with the criteria