Can criteria for a query be built off a table?

[fredalina]

If I have multiple products with various categories, and ultimately I want one report which groups and sums and categorizes appropriately for each model, can I build a table to set up the appropriate criteria for each product?

I know this can be done via VBA with Recordsets and SQL, but future users may not be as good at VBA as I (and heck, I am getting decent at it but am FAR from advanced), so I'd like to avoid having things built into VBA. If I can produce one query that includes all products appropriately summed, it will help a lot, as future users can merely define the product criteria appropriately.

The problem I'm having is with the syntax of the definitions. If criterion1 in the table is"*", the query criteria must be written as "Like [criterion1]"; however if criterion1 is "'citrus' or 'tropical'", the query produces no results for that product. Is there an example of exactly how the syntax of the criteria should be somewhere?

Thanks!

 

[GinaWhipp]

You don't want your Users to edit the query. Have a look at the below example where a form is used to set the criteria for the report.

http://www.fontstuff.com/access/acctut19.htm

 

[fredalina]

Thanks for the response.

I don't think I was very clear. The db actual has no users other than myself. I have taken on a new role with several Excel-based reports, one of which should really be written in Access, but because it's harder to find proficient Access users in our company than Excel, management would prefer to keep the reports in Excel. However, the report populates with a lot of formulas that can be cumbersome, and since Access would be a more "bulletproof" way of writing the report, my plan is to at least use Access to build a duplicate report for double-checking purposes to ensure the Excel report is coded properly. Yes, it's double work, but it's not my decision. The only future users are ones who will inherit this report from me, and they may not be as versed in Access as previously mentioned.

My hope is to lay out each product and its criteria in a table, link the table into the query by product code, and then build the queries criteria based on the linked criteria table's values for that product. Future users need only add a new product to the criteria table with the appropriate criteria for various fields. This seems do-able when a product only has up to 1 possible allowable value per criteria field. If I put in the criteria table Department = Produce, Type = Fruit, Color = *, it works fine. The problem is when I want only the Produce department, but the type can be either Fruit or Vegetable. I cannot get the syntax to work.

I have tried (in the criteria table) "Fruit" or "Vegetable", Fruit or Vegetable, "Fruit or Vegetable", "Fruit" OR "Vegetable", etc, and nothing seems to produce the same results. I would prefer not to set it up with multiple fields (i.e. Type1, Type2, Type3, ...) because it will make the table and the query very cumbersome and may not be effective.

Also, if a certain criterion is not to be used for a certain product, the wildcard "*" is used in the criteria table. This does not work if the query simply asks for [Type] instead of "Like [Type]". Is there a work-around for this?

 

[GinaWhipp]

Instead of OR try AND...

 

[fredalina]

No results.

I think I'm resigned to putting multiple fields per criterion in the criteria table (there shouldn't be more than 4), and this will work though not as clean as I'd prefer.

 

[fredalina]

Second thought, this won't work either as different summations will need to be made for different products. The Type may be important for an orange but not for an apple, whereas the color of an apple may be vital but not so for an orange.

Likes like I will need to go to VBA after all and employ some Datasets and DLookups from the Criteria table to create the SQL.

 

[GinaWhipp]

I believe you are correct... time for VBA and the like.