"OR" criteria in subform link?

[The Stoat]

Hi,

Is it possible to link parent and child forms with an "either or" criteria.

I'm currently creating the links and Sql sources for the forms using VBA -hence why this is posted here - but i need to be able to link using one or more fields .

As an example i want to display all the records where the surname is either "Smith" or the Date of Birth is #01/01/2001# or both e.g

Record 1 in the child form has an entry for field [Surname] = "Smith" and Date of Birth = #01/01/2001# so does the parent form record

Record 2 in the child form has [Surname] ="Smith" but the Date of Birth = #02/02/2002#.

Record 3 in the child form has [Surname] ="Jones" and the Date of Birth = #01/01/2001#.

I want to display all of these records in the child form.

Any ideas?

Thanks

The Stoat

 

[The Stoat]

I've managed to resolve this problem another way but i would still be interested to hear if anyone can do this.

 

[The_Doc_Man]

You might be able to do this in two steps... maybe.

Step 1. Build a UNION query that selects the same fields in both branches of the UNION - but has different WHERE clauses - and maybe even different fields as the basis of the JOINs. One selects by last name, the other by your critical date.

Step 2. Using the query wizard, build a SELECT UNIQUE query using the UNION query as input.

Once you've done this, the query from Step 2 holds what you want, I think.

Look up UNION queries in the Help files if you are not familiar with them. Also the UNIQUE keyword for your SQL.

 

[WayneRyan]

Stoat,

You can do that with a single query in design mode. I really can't
explain it, but ORs and ANDs can be expressed in the criteria.

Horizontal - AND
Vertical - OR

Wayne

 

[The Stoat]

Stoat,

You can do that with a single query in design mode. I really can't
explain it, but ORs and ANDs can be expressed in the criteria.

Horizontal - AND
Vertical - OR

Wayne

Hi Wayne,

I understand what you mean, the problem was that i could not know which field in the child form would relate to which field in the parent form. Put another way , yes i could build an "either or" select query for the child form ( this was my intention) but i needed to link on a field in the parent form that i couldn't identify until the child form was populated. Anyway thinking about it has led me to resolve a prior problem which has made this unnecessary.

The Doc Man I understand your idea. Prehaps another resolution would have been to build a compound key of the three fields in the parent form query, and the same fields in the child query i.e [ID] & [RecDate] & etc

I could then have linked the forms on this new single field? I think that would work.

Thanks for the input both of you.

The Stoat.