469,271 Members | 1,014 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

How to add Criteria 'rows' in a Query?

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
Oct 26 '11 #1
13 14364
jimatqsi
1,260 Expert 1GB
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
Oct 26 '11 #2
NeoPa
32,171 Expert Mod 16PB
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?
Oct 26 '11 #3
Hi NeoPa,

Here you have the SQL:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM News
  3. 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!
Nov 1 '11 #4
NeoPa
32,171 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. WHERE (([Launch]=[Forms]![QBF_Form_News]![Launch]))
  2.    OR (([Launch]=[Merger/Acquisition]) AND ([Launch]=[Forms]![QBF_Form_News]![Merger/Adquisition]))
  3.    OR (([Launch]=[Policy]) AND ([Launch]=[Forms]![QBF_Form_News]![Policy]))
  4.    OR (([Launch]=[Failure]) AND ([Launch]=[Forms]![QBF_Form_News]![Failure]))
  5.    OR (([Launch]=[Financial]) AND ([Launch]=[Forms]![QBF_Form_News]![Financial]))
  6.    OR (([Launch]=[Agencies]) AND ([Launch]=[Forms]![QBF_Form_News]![Agencies]))
  7.    OR (([Launch]=[Order]) AND ([Launch]=[Forms]![QBF_Form_News]![Order]))
  8.    OR (([Launch]=[Associations]) AND ([Launch]=[Forms]![QBF_Form_News]![Associations]))
  9.    OR (([Launch]=[Operators]) AND ([Launch]=[Forms]![QBF_Form_News]![Operators]))
  10.    OR (([Launch]=[GS]) AND ([Launch]=[Forms]![QBF_Form_News]![GroundSegment]));
Nov 1 '11 #5
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:

Expand|Select|Wrap|Line Numbers
  1. [News].[Launch] And [Forms]![QBF_Form_News]![Launch]
  2. [News].[Merger/Acquisition] And [Forms]![QBF_Form_News]![Merge/Adquision]
  3. [News].[Policy] And [Forms]![QBF_Form_News]![Policy]
  4. [News].[Failure] And [Forms]![QBF_Form_News]![Failure]
  5. .
  6. .
  7. .
  8. .
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 :(
Nov 2 '11 #6
May be this makes more sense to you:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM News
  3. 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
Nov 2 '11 #7
NeoPa
32,171 Expert Mod 16PB
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 :

Expand|Select|Wrap|Line Numbers
  1. WHERE ([Launch] AND [Forms]![QBF_Form_News]![Launch])
  2.    OR ([Merger/Acquisition] AND [Forms]![QBF_Form_News]![Merger/Adquision])
  3.    OR ([Policy] AND [Forms]![QBF_Form_News]![Policy])
  4.    OR ([Failure] AND [Forms]![QBF_Form_News]![Failure])
  5.    OR ([Financial] AND [Forms]![QBF_Form_News]![Financial])
  6.    OR ([Agencies] AND [Forms]![QBF_Form_News]![Agencies])
  7.    OR ([Order] AND [Forms]![QBF_Form_News]![Order])
  8.    OR ([Associations] AND [Forms]![QBF_Form_News]![Associations])
  9.    OR ([Operators] AND [Forms]![QBF_Form_News]![Operators])
  10.    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.
Nov 2 '11 #8
NeoPa
32,171 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. WHERE ([Launch] AND [Forms]![QBF_Form_News]![Launch])
  2.    OR ([Merger/Acquisition] AND [Forms]![QBF_Form_News]![Merger/Adquision])
  3.    OR ([Policy] AND [Forms]![QBF_Form_News]![Policy])
  4.    OR ([Failure] AND [Forms]![QBF_Form_News]![Failure])
  5.    OR ([Financial] AND [Forms]![QBF_Form_News]![Financial])
  6.    OR ([Agencies] AND [Forms]![QBF_Form_News]![Agencies])
  7.    OR ([Order] AND [Forms]![QBF_Form_News]![Order])
  8.    OR ([Associations] AND [Forms]![QBF_Form_News]![Associations])
  9.    OR ([Operators] AND [Forms]![QBF_Form_News]![Operators])
  10.    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).
Nov 2 '11 #9
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:

Expand|Select|Wrap|Line Numbers
  1. WHERE (([Satellite Launches].Launcher=[Forms]![QBF_Form_F]![Launcher] or [Forms]![QBF_Form_F]![Launcher] is null) 
  2. AND ([Satellite Launches].Vehicle=[Forms]![QBF_Form_F]![Vehicle] or [Forms]![QBF_Form_F]![Vehicle] is null) 
  3. AND ([Forms]![QBF_Form_F]![App]=false or ([Satellite Launches].Broadband AND [Forms]![QBF_Form_F]![Broadband]))
  4. AND ([Forms]![QBF_Form_F]![App]=false or ([Satellite Launches].Broadcasting AND [Forms]![QBF_Form_F]![Broadcasting]))
  5. 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
Nov 3 '11 #10
Hi, I have solved the error "the query is too complex" with this:

Expand|Select|Wrap|Line Numbers
  1. 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
Nov 3 '11 #11
I think I solved it with and IIF statement:

Expand|Select|Wrap|Line Numbers
  1. IIf(Nz([Forms]![QBF_Form_F]![Manufacturer],False),[Forms]![QBF_Form_F]![Manufacturer],[Satellite Launches].[Manufacturer])
Nov 3 '11 #12
NeoPa
32,171 Expert Mod 16PB
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.
Nov 11 '11 #13
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.
Apr 4 '15 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by dskillingstad | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.