Change the Join Relationship as needed

  • Thread starter Thread starter dave b via AccessMonster.com
  • Start date Start date
D

dave b via AccessMonster.com

I'm building a filter on the On Open Event in a report. The report gets its
data from a simple query. I'm giving the user the ability to select Product
ID 3 ways: List, Range, All.

If the user selects List, the selections he makes are populated in the
ProductSelection table. I've added the ProductSelection table to the query.
Now I want to change the relationship as needed; Inner Join if the user
selects List, Left Join if the user doesn't select List.
 
dave said:
I'm building a filter on the On Open Event in a report. The report gets its
data from a simple query. I'm giving the user the ability to select Product
ID 3 ways: List, Range, All.

If the user selects List, the selections he makes are populated in the
ProductSelection table. I've added the ProductSelection table to the query.
Now I want to change the relationship as needed; Inner Join if the user
selects List, Left Join if the user doesn't select List.
Click to expand...


You can not change a query without recreating the query.

I guess you could use two different queries, but that would
be clumsy at best. In this case I think all you need is to
use the Left Join and add a criteria to filter out the
non-inner join records using a criteria in your filter:

To achieve the effect of an inner join when using an outer
join, add a criteria like:
ProductSelection.ProductID Is Null
 
Back
Top