selecting two query senerios with code

[David McKnight]

In the two query senerios below I get two very different query results. The
difference between them is that in second I have added the statement.

"ON ([Filter parameters].Conference = [Season Division Conference
School].Conference)"

I would like to have code that runs the query one way or the other depending
on what I choose.


SELECT [Scores-Win Loss].ID, [Scores-Win Loss].Date, [Scores-Win
Loss].Season, [Scores-Win Loss].Home, [Scores-Win Loss].[Home Score],
[Scores-Win Loss]![Home Score]-(Coefficients![Home Field Adv Margin]/2) AS
[Home Adjusted Score], ([Scores-Win Loss]![Win-Loss])-(Coefficients![Home
Field AdvWin-Loss])/2 AS [Home Adjusted Score Win-Loss], [Scores-Win
Loss].Nuetral, [Scores-Win Loss].[Future Game], [Season Division Conference
School]!Division AS [Home Division], [Season Division Conference
School]!Conference AS [Home Conference]
FROM Coefficients, [Filter parameters] INNER JOIN ([Scores-Win Loss] INNER
JOIN [Season Division Conference School] ON ([Scores-Win Loss].Season =
[Season Division Conference School].Season) AND ([Scores-Win Loss].Home =
[Season Division Conference School].School)) ON ([Filter parameters].Division
= [Season Division Conference School].Division) AND ([Filter
parameters].Season = [Season Division Conference School].Season)
WHERE ((([Scores-Win Loss].[Future Game])=False));


SELECT [Scores-Win Loss].ID, [Scores-Win Loss].Date, [Scores-Win
Loss].Season, [Scores-Win Loss].Home, [Scores-Win Loss].[Home Score],
[Scores-Win Loss]![Home Score]-(Coefficients![Home Field Adv Margin]/2) AS
[Home Adjusted Score], ([Scores-Win Loss]![Win-Loss])-(Coefficients![Home
Field AdvWin-Loss])/2 AS [Home Adjusted Score Win-Loss], [Scores-Win
Loss].Nuetral, [Scores-Win Loss].[Future Game], [Season Division Conference
School]!Division AS [Home Division], [Season Division Conference
School]!Conference AS [Home Conference]
FROM Coefficients, [Filter parameters] INNER JOIN ([Scores-Win Loss] INNER
JOIN [Season Division Conference School] ON ([Scores-Win Loss].Season =
[Season Division Conference School].Season) AND ([Scores-Win Loss].Home =
[Season Division Conference School].School)) ON ([Filter
parameters].Conference = [Season Division Conference School].Conference) AND
([Filter parameters].Division = [Season Division Conference School].Division)
AND ([Filter parameters].Season = [Season Division Conference School].Season)
WHERE ((([Scores-Win Loss].[Future Game])=False));

 

[Guest]

you will have to have both querys. run one query depending
on what you choose then run the other.
you can't have 1 queries with 2 different joins.

 

[Dale Fye]

What is this code supposed to do?
I would save these queries as Qry1 and Qry2 (I'd actually use names that are
much more meaningful). I'm going to assume you have an option group (frame)
on your form that contains two radio buttons, one for the first query, the
second for the second query, and that you have a listbox that you want to use
as the place where the results of these queries will be displayed. In that
case, in the AfterUpdate event of your option group, I would put some code
that looks similiar to:

Private Sub og_ListSource_AfterUpdate

if me.og_ListSource = 1 then
me.lst_YourListBox.controlsource = Qry1
else
me.lst_YourListBox.controlsource = Qry2
endif

End sub

HTH
Dale

In the two query senerios below I get two very different query results. The
difference between them is that in second I have added the statement.

"ON ([Filter parameters].Conference = [Season Division Conference
School].Conference)"

I would like to have code that runs the query one way or the other depending
on what I choose.


SELECT [Scores-Win Loss].ID, [Scores-Win Loss].Date, [Scores-Win
Loss].Season, [Scores-Win Loss].Home, [Scores-Win Loss].[Home Score],
[Scores-Win Loss]![Home Score]-(Coefficients![Home Field Adv Margin]/2) AS
[Home Adjusted Score], ([Scores-Win Loss]![Win-Loss])-(Coefficients![Home
Field AdvWin-Loss])/2 AS [Home Adjusted Score Win-Loss], [Scores-Win
Loss].Nuetral, [Scores-Win Loss].[Future Game], [Season Division Conference
School]!Division AS [Home Division], [Season Division Conference
School]!Conference AS [Home Conference]
FROM Coefficients, [Filter parameters] INNER JOIN ([Scores-Win Loss] INNER
JOIN [Season Division Conference School] ON ([Scores-Win Loss].Season =
[Season Division Conference School].Season) AND ([Scores-Win Loss].Home =
[Season Division Conference School].School)) ON ([Filter parameters].Division
= [Season Division Conference School].Division) AND ([Filter
parameters].Season = [Season Division Conference School].Season)
WHERE ((([Scores-Win Loss].[Future Game])=False));


SELECT [Scores-Win Loss].ID, [Scores-Win Loss].Date, [Scores-Win
Loss].Season, [Scores-Win Loss].Home, [Scores-Win Loss].[Home Score],
[Scores-Win Loss]![Home Score]-(Coefficients![Home Field Adv Margin]/2) AS
[Home Adjusted Score], ([Scores-Win Loss]![Win-Loss])-(Coefficients![Home
Field AdvWin-Loss])/2 AS [Home Adjusted Score Win-Loss], [Scores-Win
Loss].Nuetral, [Scores-Win Loss].[Future Game], [Season Division Conference
School]!Division AS [Home Division], [Season Division Conference
School]!Conference AS [Home Conference]
FROM Coefficients, [Filter parameters] INNER JOIN ([Scores-Win Loss] INNER
JOIN [Season Division Conference School] ON ([Scores-Win Loss].Season =
[Season Division Conference School].Season) AND ([Scores-Win Loss].Home =
[Season Division Conference School].School)) ON ([Filter
parameters].Conference = [Season Division Conference School].Conference) AND
([Filter parameters].Division = [Season Division Conference School].Division)
AND ([Filter parameters].Season = [Season Division Conference School].Season)
WHERE ((([Scores-Win Loss].[Future Game])=False));