Search for records that meet multiple crieria on 'many side'?

[geralf]

I have two tbles with a 1 to many relationship. Lets name them...tblOne and tblMany. The fielf they are linked together with, is a long numeric, I'll call it LinkField in both tables.

I've set up a form to enter criteria for a query I'm going to run. The query is created, using the CreateQueryDef Method. I'm building a criteria string so the right records are pulled. The criteria which makes me pull my hair (well,whats left of it )is this:

In a 1 to many relationship, you can for instance have five records in the tblMany, with the foreign key from tblOne. I'm searching for lets say two items on the tblMany which meet the criterias and has the same foreign key. This record from the tblOne side I want pulled. This is however no easy task, since each record in the tblMany only lists one search item for each record. So setting ...'WHERE Item=2 AND Item=5....won't work since each record in tblMany only lists one item. I need that foreign key value which meets the criteria set from the form(this is a multi select listbox). So, is there any easy way out of this, or am I looking at some heftu stringbuilding wit use of Instr to get the records?

I don't know if I've explained myself any good here, so please ask additional questions if you need. If you have any suggestions, solutions or ideas, I'd like to hear of them.

Thanks in advance.


Specs: Win2000, Access 2000

 

[EMP]

setting ...'WHERE Item=2 AND Item=5....won't work since each record in tblMany only lists one item.

What you need is... 'WHERE Item=2 OR Item=5 ....

You can also use the IN operator... 'WHERE Item IN (2,5, ...)


I have attached some code for building an IN string from a multiselect listbox.

 

[geralf]

Thank you both for your replies.

Pat Hartman, you've understood my problem correct. I'll try it out first thing when I get back to my projrct. Thanks for your help (once again). Very much appreciated.

 

[geralf]

Ok, I've made the queries and it's BRILLIANT in it's simplicity!
Works Perfect.

Thanks again Pat.

Now to the process of making this a dynamic query to work with my search form......