How to add Criteria 'rows' in a Query?

[cristina m]

Hi,

I want to do a Select Query without VBA, just using the wizard. The problem that I find is that I need to do more "OR" statements than 'rows'of criteria. Is there a way to add new 'rows'of criteria?
I have 9 rows to do "OR" statement, but I need to do 10 "OR" statements.

Cristina

[jimatqsi]

You can just type "or" in the criteria. Technically you could do all 9 or more ORs in one row of criteria. That actually is better because of the way the wizard acts if you have more than one column being tested. It may not combine the tests on the columns the way you think it will.

Or you could just right-click in the upper half of the wizard and go into SQL mode and type away. After you see the format of the SQL for the first 9 you'll understand how to add X more ORs. That's how many of us learned to code SQL.

Jim

[NeoPa]

If you have that many ORs in your logic then there's a (very) good chance that you're not designing it correctly in the first place.

What exactly are you trying to do?

Why not post some of your current SQL in the question to illustrate what you're trying to say?

[cristina m]

Hi NeoPa,

Here you have the SQL:

SELECT News.Launch, News.Failure, News.Order, News.[Merger/Acquisition], News.Policy, News.Financial, News.Agencies, News.Associations, News.Operators, News.GS, News.NewsName, News.Link
FROM News
WHERE (((News.Launch)=[News].[Launch] And (News.Launch)=[Forms]![QBF_Form_News]![Launch])) OR (((News.Launch)=[News].[Merger/Acquisition] And (News.Launch)=[Forms]![QBF_Form_News]![Merge/Adquision])) OR (((News.Launch)=[News].[Policy] And (News.Launch)=[Forms]![QBF_Form_News]![Policy])) OR (((News.Launch)=[News].[Failure] And (News.Launch)=[Forms]![QBF_Form_News]![Failure])) OR (((News.Launch)=[News].[Financial] And (News.Launch)=[Forms]![QBF_Form_News]![Financial])) OR (((News.Launch)=[News].[Agencies] And (News.Launch)=[Forms]![QBF_Form_News]![Agencies])) OR (((News.Launch)=[News].[Order] And (News.Launch)=[Forms]![QBF_Form_News]![Order])) OR (((News.Launch)=[News].[Associations] And (News.Launch)=[Forms]![QBF_Form_News]![Associations])) OR (((News.Launch)=[News].[Operators] And (News.Launch)=[Forms]![QBF_Form_News]![Operators])) OR (((News.Launch)=[News].[GS] And (News.Launch)=[Forms]![QBF_Form_News]![GroundSegment]));

I did what jimatqsi said and it worked.

Thanks!

[NeoPa]

There are some problems with that Cristina.

I have redone your WHERE clause less verbosely for you to see more easily exactly what you're specifying. It seems a little weird, but I assume you know what you're doing. Essentially the [Launch] field must match one of the controls on the form and it must also match the field in the record with the same name as that form control. I don't pretend to understand why that's what is required, but that is what the SQL is saying.

I simply tidied up the SQL to make it more visible - except - I fixed the spelling of the [Merger/Adquisition] control and removed a check that Launch= [Launch]. Otherwise it was about saying the same thing more tidily so you can more easily understand what's there.

See if you think it's what you want :

WHERE (([Launch]=[Forms]![QBF_Form_News]![Launch]))
   OR (([Launch]=[Merger/Acquisition]) AND ([Launch]=[Forms]![QBF_Form_News]![Merger/Adquisition]))
   OR (([Launch]=[Policy]) AND ([Launch]=[Forms]![QBF_Form_News]![Policy]))
   OR (([Launch]=[Failure]) AND ([Launch]=[Forms]![QBF_Form_News]![Failure]))
   OR (([Launch]=[Financial]) AND ([Launch]=[Forms]![QBF_Form_News]![Financial]))
   OR (([Launch]=[Agencies]) AND ([Launch]=[Forms]![QBF_Form_News]![Agencies]))
   OR (([Launch]=[Order]) AND ([Launch]=[Forms]![QBF_Form_News]![Order]))
   OR (([Launch]=[Associations]) AND ([Launch]=[Forms]![QBF_Form_News]![Associations]))
   OR (([Launch]=[Operators]) AND ([Launch]=[Forms]![QBF_Form_News]![Operators]))
   OR (([Launch]=[GS]) AND ([Launch]=[Forms]![QBF_Form_News]![GroundSegment]));

[cristina m]

Hi NeoPa,

Than you for your help. I am not sure you understood that I want to do.

I have a table called 'News' with a yes/no field for each type of news: launch, merger/Acquisition, Policy, Failure...(An entry can have more than one type, so more than one field set as yes)

In orger to filter the News by type, I have a QBF_Form where the user selects with checks box which type of news to be filtered as an OR. So if the user select launch and Policy, all the news related with Launch and all the news related with Policy will be shown.

So, when I run the query to filter, I have to check that both the check box in the form and the field in the table are both yes.

I did not wrote thtat SQL myself, I just wrote this in the criteria:

[News].[Launch] And [Forms]![QBF_Form_News]![Launch]
[News].[Merger/Acquisition] And [Forms]![QBF_Form_News]![Merge/Adquision]
[News].[Policy] And [Forms]![QBF_Form_News]![Policy]
[News].[Failure] And [Forms]![QBF_Form_News]![Failure]
.
.
.
.

One critaria per row as an OR

And the code that I pased is the code that Access generated with my criteria in the design view.

It works like that, so its ok.

However, when I want to complicated the QBF_Form, and I want to include other fields to filter, I get an error massage saying than the query is too complex :(

[cristina m]

May be this makes more sense to you:

SELECT News.Launch, News.Failure, News.Order, News.[Merger/Acquisition], News.Policy, News.Financial, News.Agencies, News.Associations, News.Operators, News.GS, News.NewsName, News.Link
FROM News
WHERE (((News.Launch)=True And (News.Launch)=[Forms]![QBF_Form_News]![Launch])) OR (((News.Failure)=True And (News.Failure)=[Forms]![QBF_Form_News]![Failure])) OR (((News.Order)=True And (News.Order)=[Forms]![QBF_Form_News]![Order])) OR (((News.[Merger/Acquisition])=True And (News.[Merger/Acquisition])=[Forms]![QBF_Form_News]![Merge/Adquision])) OR (((News.Policy)=True And (News.Policy)=[Forms]![QBF_Form_News]![Policy])) OR (((News.Financial)=True And (News.Financial)=[Forms]![QBF_Form_News]![Financial])) OR (((News.Agencies)=True And (News.Agencies)=[Forms]![QBF_Form_News]![Agencies])) OR (((News.Associations)=True And (News.Associations)=[Forms]![QBF_Form_News]![Associations])) OR (((News.Operators)=True And (News.Operators)=[Forms]![QBF_Form_News]![Operators])) OR (((News.GS)=True And (News.GS)=[Forms]![QBF_Form_News]![GS]));

That works ok.

About the error saying that the query is too complex, what can I do about it?

Thank you

[NeoPa]

Cristina:
Than you for your help. I am not sure you understood that I want to do.

No. Of course not. You haven't explained what you want to do in your question. I'm only telling you what your posted code is telling me. I made it easier to read for you so that you could understand what your code is doing and realise it's not what you actually want (I don't actually know what you want but I would guess it's not what you have).

Cristina:
It works like that, so its ok.

No. It doesn't. Your testing is flawed if you think that code would do that job correctly. I appreciate you didn't design it in SQL directly, but you did it wrongly in the QBD grid, so it's still wrong.

Now I know what you were trying to do I can post an example of the SQL code that you want. If you put this into your query in SQL View then switch over to Design View then you will see how you should have specified the criteria in you QBE originally and then you'll know how to do such things in future. It's easier for me to show the SQL than a great, long, explanation of how to change the query in Design View :

WHERE ([Launch] AND [Forms]![QBF_Form_News]![Launch])
   OR ([Merger/Acquisition] AND [Forms]![QBF_Form_News]![Merger/Adquision])
   OR ([Policy] AND [Forms]![QBF_Form_News]![Policy])
   OR ([Failure] AND [Forms]![QBF_Form_News]![Failure])
   OR ([Financial] AND [Forms]![QBF_Form_News]![Financial])
   OR ([Agencies] AND [Forms]![QBF_Form_News]![Agencies])
   OR ([Order] AND [Forms]![QBF_Form_News]![Order])
   OR ([Associations] AND [Forms]![QBF_Form_News]![Associations])
   OR ([Operators] AND [Forms]![QBF_Form_News]![Operators])
   OR ([GS] AND [Forms]![QBF_Form_News]![GroundSegment]);

NB. This version assumes that [Forms]![QBF_Form_News]![Merger/Adquision] is still mis-spelled in your design. If you fix that then you should also fix the spelling in your SQL.

[NeoPa]

Cristina:
That works ok.

So it should, yes. This may be a weird Access work-around (It's not a natural of checking the values but would return the same results) but may be an easier way to handle things in the QBE grid I expect. It's only slightly more convoluted at the SQL level.

Cristina:
About the error saying that the query is too complex, what can I do about it?

Good question. I assume that you have it working for 10 field checks but adding any more exceeds Access's QBE limit (I actually found the limit was 9 when I tried it out). Try out the SQL I posted, but if that doesn't work (It did for me) continue to the next paragraph.

In that case (The above didn't work) I would define new fields with your logic in them and simply test that in the WHERE clause (The SELECT field needn't be made visible). In SQL you would simply add it into the WHERE clause with :

WHERE ([Launch] AND [Forms]![QBF_Form_News]![Launch])
   OR ([Merger/Acquisition] AND [Forms]![QBF_Form_News]![Merger/Adquision])
   OR ([Policy] AND [Forms]![QBF_Form_News]![Policy])
   OR ([Failure] AND [Forms]![QBF_Form_News]![Failure])
   OR ([Financial] AND [Forms]![QBF_Form_News]![Financial])
   OR ([Agencies] AND [Forms]![QBF_Form_News]![Agencies])
   OR ([Order] AND [Forms]![QBF_Form_News]![Order])
   OR ([Associations] AND [Forms]![QBF_Form_News]![Associations])
   OR ([Operators] AND [Forms]![QBF_Form_News]![Operators])
   OR ([GS] AND [Forms]![QBF_Form_News]![GroundSegment])

When you view this in QBE you'll easily see how to do it without using SQL.

PS. I just realised this solution is the same as the previous one (Ooops). Can you just let us know if this works or not. If not, then please give details (The whole SQL properly posted would be a good thing to include).

[cristina m]

Hi,

Thanks for your help.

I tried that SQL and it works. However, when I try to include more criteria, it keeps telling me that the query is too complex.

In essence, what I need to do is a 'autofilter' like in Excel with Satellite Launches table.

What I am trying to do now is a QBFquery where the user selects what to filter, as AND statements. The field to filter comes from the 'Satellite Launches' table. Some fields are shown in the user form as combobox (Launcher, Vehicle...) , and some others are checkboxs like in my previous question (APPLICATIONS: Broadband (yes/no), Broadcasting (yes/not, Military(yes/not)...)To know if the user is willing to filter by the application, I inserted a check box 'filter by application'(App).
The user may decide to filter by all those fields or just one or few, then, some fields can be 'null' if the user dont choose any from the checkbox or combobox. So, in my Query criteria, I need to say something like:

WHERE (([Satellite Launches].Launcher=[Forms]![QBF_Form_F]![Launcher] or [Forms]![QBF_Form_F]![Launcher] is null) 
AND ([Satellite Launches].Vehicle=[Forms]![QBF_Form_F]![Vehicle] or [Forms]![QBF_Form_F]![Vehicle] is null) 
AND ([Forms]![QBF_Form_F]![App]=false or ([Satellite Launches].Broadband AND [Forms]![QBF_Form_F]![Broadband]))
AND ([Forms]![QBF_Form_F]![App]=false or ([Satellite Launches].Broadcasting AND [Forms]![QBF_Form_F]![Broadcasting]))
AND ([Forms]![QBF_Form_F]![App]=false or ([Satellite Launches].Military AND [Forms]![QBF_Form_F]![Military]))

When I try that, I got the error saying that the query is too complex. the problem might be that I am doing a wrong user Form squeme, but I can not think of a different way to ask user the parameter to filter and use them in the query. In essence, what I need to do is a 'autofilter' like in Excel with Satellite Launches table. I read that in Access 2007 you have that choice, but I am using 2003. Any suggestion which does not include VBA is welcome (I havent VBA skills :().

Thank you for your help

[cristina m]

Hi, I have solved the error "the query is too complex" with this:

WHERE (([Satellite Launches].Launcher=Nz([Forms]![QBF_Form_F]![Launcher] ,[Satellite Launches].Launcher) AND ...

However, I found a new problem: if in the user form, I select a field to filter by mistake, and then I unselect it, it does not work, because it is not null anymore, but it is 0, so it does not filter the way I want. How can I fix that??

Thank you

[cristina m]

I think I solved it with and IIF statement:

IIf(Nz([Forms]![QBF_Form_F]![Manufacturer],False),[Forms]![QBF_Form_F]![Manufacturer],[Satellite Launches].[Manufacturer])

[NeoPa]

Cristina, I come back to look at this in detail a week or so later (apologies for the delay), but I find I can make little or no sense of most of what you say as you keep skipping from one problem to another. I can't tell what is related to your original question (which is the only one we're allowed to deal with in this thread). So I hope you managed to find workable solutions.

If not, then please post your current situation, as regards this question, in here or for any other questions in their own, new, threads.

[glenng3]

MS Access 2013 still has this same problem with limited criteria rows in the graphical screen. If you do as NeoPa suggests and drop into SQL and edit the SQL with the clauses you want to add (this is pretty easy to do), then go back to the Graphical screen, you will see that MS Access adds more criteria rows.