8 selection criteria fields (1 Viewer)

[John liem]

I have a query which has 8 criteria fields, meaning that I will need 128 grid rows to build complete conditions with OR and AND functions to cover all the possible conditions from the selection criteria fields.
How many query grid rows I can use without exceeding the maximum rows. I have tried with 6 fields (32 rows), it gives me "Query too complex", run-time error 3360 as result.
How can I do this? Thanks.

 

[Mile-O]

Can you explain this further?

 

[John liem]

What I am trying to achieve is to run a query with 8 field selection criteria which I can choose using a dropdown fields in a form "Fm_Selections". This Form has a button to run the query Qy_Selection using Tbl_Main. The result of this query will show the records which match the criteria, which have been chosen in the Fm_Selection. If the selection fields are blank, all records will be output.

 

[lledar]

all you need in the SQL View

SELECT table1.categoryinquery
FROM table1
WHERE (([Categoryinquery]=[forms]![Formname]![comboboxname] Or [forms]![Formname]![comboboxname] Is Null)=True)


And of course the your table with your 8 values is linked to your form

 

[John liem]

I think, I haven't explain it right, the selection criteria has 8 fields: name, address, city, country, region, sub-region, organization and company. Depends on what I have chosen in each field from the 8 criteria, the query will result the right record(s).

 

[Ukraine82]

It seems to me that you're trying to make an individual criteria for each fields in a query. If so, thought about making a cascading combobox.

Take a look at the attachment file.

hth,
Michael

 

[John liem]

In your example, you are only using 3 criteria in 3 different comboboxes. The query1 will only have 8 criteria lines.
What will happen than if I am using all my 8 criteria fields in 8 different comboboxes, The query1 would have like 256 lines of criteria lines which Access can not handle.

 

[Mile-O]

Look up QueryDef - you'll see how to build a query based on criteria. I believe Pat Hartman has posted a few examples somewhere. Myself also.

 

[lledar]

Make a Table of the 8 values call it table1
Design your form and link the combobox to the table1
In you query you tell the Criteria to equal the value entered in the form combo box. The code I worte above is the code you need. I just have it viewed in SQL view for you.

 

[John liem]

Hi lledar, appreciate and thanks for your help. It works as I want.