Query with Multi inputs

[kpouya]

ok thanks i will try that and let you know.

[kpouya]

hey
well i wrote this in my SQL of the Query i am running

SELECT Libary.Title, Libary.LastName , Libary.FirstNam e, Libary.Company, Libary.Industry , Libary.Organiza tionLayout, Libary.MajorPro ccess, Libary.SubProcc ess, Libary.Summary, Libary.Attachme nt
FROM Libary
WHERE (((Libary.Indus try)=[forms]![SearchByMore]![Industry]) AND ((Libary.Organi zationLayout)=[forms]![SearchByMore]![Organizational Layout]) AND ((Libary.MajorP roccess)=[forms]![SearchByMore]![Major Proccess]) AND ((Libary.SubPro ccess)=[forms]![SearchByMore]![Sub Proccess]));

but when i go into the form to do a query, it returns nothing.

[Stewart Ross]

Before RuralGuy comes back to you, my immediate response would be 'and what have you done to check what your query is doing'? I guess RuralGuy could try to divine this on your behalf - but his powers may well not extend to divining answers at a distance without your database in front of him. Nor do mine...

You will need to at least verify the query by checking what happens to each form field criterion one by one. Start by removing the three form field criteria and ensure that the query returns all rows. If it does, put one of the criteria back in and see if the criteria selects what you expect. Then put in the second to see what happens. Finally add the third if the first two work. At some stage one of these criteria will cause a failure.

I am assuming you have some values in the form controls you are referring to when you run the query, as if you don't the query is bound not to return any values. You are running it AFTER you select from the combos, aren't you?

We expect you to help us to help you, by running logical tests that will at least narrow down the possibilities - just saying 'it does not work' or 'it returns nothing' is not at all helpful!

-Stewart

[kpouya]

well i have done that
as soon as insert a second crieteria it doesnt return anything

[Stewart Ross]

And what have you done about that? Surely this is telling you that your comparison is either to fields that have different values, or to a null? It is impossible for us to debug your query at this end without access to your data, your code, the order in which you are doing things and so on.

Please think what you are expecting us to do to help you before you post! We cannot simply divine the state of your system with no test data to assist us - and no reports from you of what the system state is.

You really must do some more systematic investigation of your criteria and how they are being applied before you post again. Otherwise I feel I must close this thread, as there is no way we can assist when all we know is 'the second criterion does not work'.

-Stewart

[OldBirdman]

I created a table=Libary with only the fields (all Memo) as shown in the query SQL statement

SELECT Libary.Title, Libary.LastName , Libary.FirstNam e, Libary.Company, Libary.Industry , Libary.Organiza tionLayout, Libary.MajorPro ccess, Libary.SubProcc ess, Libary.Summary, Libary.Attachme nt
FROM Libary
WHERE (((Libary.Indus try)=[forms]![SearchByMore]![Industry]) AND ((Libary.Organi zationLayout)=[forms]![SearchByMore]![Organizational Layout]) AND ((Libary.MajorP roccess)=[forms]![SearchByMore]![Major Proccess]) AND ((Libary.SubPro ccess)=[forms]![SearchByMore]![Sub Proccess]));

as supplied by kpouya in Post#17. This was then copied into the SQL view of Query Design.
I created a form with the 4 form controls used in above SQL.
I initialized the table with various numbers, from 1 to 9
I entered various combinations of these numbers on my form
I switched the Query Design View to Datasheet View, and in all cases it works.

[RuralGuy]

Thanks for jumping in guys. I welcome the assistance. I was away from the computer today.

[kpouya]

hey OldBirdman
were you able to select one option from every combobox and run the query and see if that works?

[OldBirdman]

I didn't keep my tests, but yes, as there was nothing in the SQL statement to handle a null value in any of the form objects. So what I wouldn't have done was test with fewer than 4 fields, or with invalid fields. I remember testing with combinations that returned zero records, which caused errors, but I wasn't trying make a program work, just verify that the logic presented was valid.

[kpouya]

cause right now
i checked all my stuff but if i choose like 2 out of four options, it returns null.
so if i get this right i have to have a value in all four to return a result?

[OldBirdman]

Not at home today, so I'll check recycle bin tomorrow & see if I still have.

If you paste your SQL statement to query design on a new query, it should give you the correct data. If not, try working in desin view to see what is wrong, or what you need to change.

I'll look further tomorrow. Good luck

[OldBirdman]

Although I kept my test database, I did not keep any results of testing. I have a form, a query, and a table. These are the simplist that I could produce that would allow me to paste the SQL statement as shown by kpouya in post #17 and get meaningful results while in Query Design.

The query (from post #17):

SELECT Libary.Title, Libary.LastName , Libary.FirstNam e, Libary.Company, Libary.Industry , Libary.Organiza tionLayout, Libary.MajorPro ccess, Libary.SubProcc ess, Libary.Summary, Libary.Attachme nt
FROM Libary
WHERE (((Libary.Indus try)=[forms]![SearchByMore]![Industry]) AND ((Libary.Organi zationLayout)=[forms]![SearchByMore]![Organizational Layout]) AND ((Libary.MajorP roccess)=[forms]![SearchByMore]![Major Proccess]) AND ((Libary.SubPro ccess)=[forms]![SearchByMore]![Sub Proccess]));

I have a table = "Libary" with the fields (all memo) used by the above query.
I have a form = "SearchByMo re" with the controls used by the above query. This form has 4 unbound comboboxes for entering the query criteria. These comboboxes do not cascade, as that has nothing to do with the query.

Have you done the same kind of testing that I have, using the Query Design, with the design grid and the SQL window?
How are you using this query to change your form?
How do you select 2 of 4 comboboxes if they cascade? Notice that the original problem was stated as

so they choose first option
then second option which its selection is based on first combo box
then choose third option which its selection is based on second combobox selection

As stated, ALL criteria must be selected. Original problem had 3 criteria, not 4, so the problem got more complex before the simplier one was working.
What values are in your comboboxes if they are not selected?

cause right now
i checked all my stuff but if i choose like 2 out of four options, it returns null.
so if i get this right i have to have a value in all four to return a result?

If "it returns null" means that it returns a recordset with zero records, then that is what the query finds, running your table against your criteria.

When I entered this thread, it was because I could see that the query, as presented, worked. My table has just the fields used by the query. My form has just the fields used by the query. Whether the controls are textboxes, comboboxes, listboxes, etc. makes no difference to the query. Whether the fields in the table are memo, text, etc. makes no difference to the query.
You want to change what the query does, but you don't post what you are trying.

I have no further answers for you than Stewart Ross Inverness in post #20:

And what have you done about that? Surely this is telling you that your comparison is either to fields that have different values, or to a null? It is impossible for us to debug your query at this end without access to your data, your code, the order in which you are doing things and so on.

Please think what you are expecting us to do to help you before you post! We cannot simply divine the state of your system with no test data to assist us - and no reports from you of what the system state is.

You really must do some more systematic investigation of your criteria and how they are being applied before you post again.