How to make a query dependent on another query (example attached)?

[WannabePrgmr]

I am working in Access 2003 and have approximately 10 fields that I want to be able to query on.
The catch is that if I only fill in 3 or 4 fields, only the data that matches only those three fields will be displayed (fields left blank will be ignored).
I know the "Is Null" will work in a different way, but I need only filled in fields to display.

I fouond on another thread somewhere this example db that shows how it's done, by running several queries one after another by making the beginning of one dependent on the other.
The problem is that I don't know how they did it! If I look at the query2 in the attached, and look at the object dependencies, it shows query1.

I can't figure out how they made that happen. Nothing I do will cause one query to run from another....

Thanks!!!

[nico5038]

The general approach I us is to take a UNION query and thus add a "<All>" option. This option is "translated " by the second combo query column into a "*", thus enabling a LIKE statement in the query.
You'll need to force the user to make a selection, thus the OnCurrent event of the form will fill the combo's with the "<All>" value and before activating the query it's tested that all combo's have a value.

The query doesn't need any "sub queries", thus I've eliminated them.
Check out this working sample.

Nic;o)

[WannabePrgmr]

Hey Nico, I hope you're not getting tired of me thanking you, but thank you!

I copied everything I saw over to my actual db and I have 10 combo boxes (not that that should matter).

I go to click on the first cbo box and the folloing error comes up:

"The number of columns in the two selected tables or queries of a union query do not match".

[nico5038]

Check my queries. They hold for the "original" table twice the same column and for the "<All>" an additional leading "*".
The column widths are set to 0 so the first (bound) column is "hidden" and that's the column to be used by the query.

Clear ?

Nic;o)

[nico5038]

I forgot to mention that I never code this on tables, but instruct the user to use the right click pop-up form. This will allow every combination needed and as a service I sometimes "echo" the used Filter on the form above the datasheet. (I normally use a mainform with a datasheet subform for this.)
Check e.g. this sample: http://bytes.com/attachments/attachm...ction-2000.zip

Nic;o)

[WannabePrgmr]

Nico, I have it functioning, but say I choose <All> for 9 of the combo boxes and just want to search the name "Sam" in the "Name" combo box, it will query for Sam, along with every name field that was left blank! I'm assuming that has to do with the "Or Is Null". But I believe that if that is not in there, it wont bring back anything????

I'll try it without the "Or Is Null in the query criteria and see what happens....

Also, is there a way to make the <All> the default for each box, that way the user only has to change what they need, instead of going through and choosing <All> from each box?

Thanks

[WannabePrgmr]

Like I thought, the "Or Is Null" removed doesn't bring anything back, as I'm 100% sure you already knew!

[nico5038]

I did set the three combo´s to <All> in my sample. Check the OnCurrent event.

You can drop the "Or Is Null" for every fully filled column, but else you might miss data when querying multiple columns...

Nic;o)

[WannabePrgmr]

Nico, sorry I left this hanging for so long....I had to put out other fires for a while!

In your example, the three boxes don't have <All> as a default, I have to drop down and choose it for each (it is the first option though).

Everything works great with my ten boxes now except that <All> has to be chosen and isn't a default. Any ideas?

Thanks

[nico5038]

Oops, I see a typo in my solution, check this attachment.

Nic;o)

[WannabePrgmr]

OK, I see now. For some reason, 9 of my 10 boxes have a blank space as the 1st option when you drop them down (meaning the second option is "<All>, which is not the default for that reason)!

I've tried sorting in ascending order in the query to try and get rid of it but nothing works! Only one has no space and does have "<All>" as a default??

So close.......

[nico5038]

Just add to each query a "Not Null" condition for the "bound" column or add a description when there's an ID, but no description.

Nic;o)

[WannabePrgmr]

Sorry to be such a pest, but could you give an example? I've put into each query a "Is Not Null" and nothing changed?

[WannabePrgmr]

More specifically, how do I add a "Not Null" condition to the "bound column" or where do I add a description?
Thanks

[nico5038]

Just open the query that's the record source of the combo box.
It's the query with the union. For testing create a new empty query and paste just the "real" query text without the "<All>" union part and add the Not Null as criteria.
When there's no longer an empty line than replace that part in the original query.

Nic;o)