parameter query from combo box

[MTstraw]

I am trying to create a parameter query where the info to be looked up was
entered via a combo box (i.e. Month, Year). Just a little insight: I am
creating a year end report and put in a combo box for the month so that info
in that column will not be entered wrong and therefore doesn't show up. When
I try to run a parameter query for the month nothing shows up.

 

[Ofer Cohen]

Can you post your SQL?

Does the criteria in the query for the date look like

Forms![FormName]![ComboName]

 

[MTstraw]

SELECT [Aircraft Transfers].*, [Airspace Totals].*, [CAG/SFARP].*, [CNI
Ashore].*, [Community Service].*, [Crewmember Hours].*, [Fuel Pit Report].*,
[Individual Unit].*, [Runway Totals].*, [SAR Missions].*, [SAR Sortie
Totals].*, [A/C Accident].*, [Field Hours].*, [Air Terminal Processing].*,
Airfield.*, Arrestments.*
FROM ((([Fuel Pit Report] INNER JOIN [A/C Accident] ON [Fuel Pit
Report].[Report Month] = [A/C Accident].[Report Month]) INNER JOIN (([Runway
Totals] INNER JOIN [SAR Missions] ON [Runway Totals].[Report Month] = [SAR
Missions].[Report Month]) INNER JOIN [SAR Sortie Totals] ON [SAR
Missions].[Report Month] = [SAR Sortie Totals].[Report Month]) ON [A/C
Accident].[Report Month] = [Runway Totals].[Report Month]) INNER JOIN
((([Field Hours] INNER JOIN Airfield ON [Field Hours].[Report Month] =
Airfield.[Report Month]) INNER JOIN [Individual Unit] ON Airfield.[Report
Month] = [Individual Unit].[Report Month]) INNER JOIN ((((([Aircraft
Transfers] INNER JOIN [Airspace Totals] ON [Aircraft Transfers].[Report
Month] = [Airspace Totals].[Report Month]) INNER JOIN [CAG/SFARP] ON
[Airspace Totals].[Report Month] = [CAG/SFARP].[Report Month]) INNER JOIN
[CNI Ashore] ON [CAG/SFARP].[Report Month] = [CNI Ashore].[Report Month])
INNER JOIN [Community Service] ON [CNI Ashore].[Report Month] = [Community
Service].[Report Month]) INNER JOIN [Crewmember Hours] ON [Community
Service].[Report Month] = [Crewmember Hours].[Report Month]) ON [Individual
Unit].[Report Month] = [Crewmember Hours].[Report Month]) ON [SAR Sortie
Totals].[Report Month] = [Field Hours].[Report Month]) INNER JOIN ([Air
Terminal Processing] INNER JOIN Arrestments ON [Air Terminal
Processing].[Report Month] = Arrestments.[Report Month]) ON [Fuel Pit
Report].[Report Month] = [Air Terminal Processing].[Report Month]
WHERE ((([Aircraft Transfers].[Report Month])=[Month]) AND (([Airspace
Totals].[Report Month])=[Month]) AND (([CAG/SFARP].[Report Month])=[Month])
AND (([CNI Ashore].[Report Month])=[Month]) AND (([Community Service].[Report
Month])=[Month]) AND (([Crewmember Hours].[Report Month])=[Month]) AND
(([Individual Unit].[Report Month])=[Month]) AND ((Airfield.[Report
Month])=[Month]) AND (([Field Hours].[Report Month])=[Month]) AND (([SAR
Sortie Totals].[Report Month])=[Month]) AND (([SAR Missions].[Report
Month])=[Month]) AND (([Runway Totals].[Report Month])=[Month]) AND (([A/C
Accident].[Report Month])=[Month]) AND (([Fuel Pit Report].[Report
Month])=[Month]) AND (([Air Terminal Processing].[Report Month])=[Month]) AND
((Arrestments.[Report Month])=[Month]));

Can you post your SQL?

Does the criteria in the query for the date look like

Forms![FormName]![ComboName]

--
Good Luck
BS"D

I am trying to create a parameter query where the info to be looked up was
entered via a combo box (i.e. Month, Year). Just a little insight: I am
creating a year end report and put in a combo box for the month so that info
in that column will not be entered wrong and therefore doesn't show up. When
I try to run a parameter query for the month nothing shows up.

 

[Ofer Cohen]

From your query I can see that you linked all the tables using the month
field, so there is no need to create a criteria under each month field,
filter on one and the join will take care of the rest.

The criteria [Month] need to be a path to the form

Forms![FormName]![ComboName]

Or, base the report on the full SQL with no criteria, and then open the
report using the WhereCondition of the OpenReport Command line

Docmd.OpenReport "ReportName" , , , "[Aircraft Transfers].[Report Month]=" &
Me.[ComboName]

--
Good Luck
BS"D

SELECT [Aircraft Transfers].*, [Airspace Totals].*, [CAG/SFARP].*, [CNI
Ashore].*, [Community Service].*, [Crewmember Hours].*, [Fuel Pit Report].*,
[Individual Unit].*, [Runway Totals].*, [SAR Missions].*, [SAR Sortie
Totals].*, [A/C Accident].*, [Field Hours].*, [Air Terminal Processing].*,
Airfield.*, Arrestments.*
FROM ((([Fuel Pit Report] INNER JOIN [A/C Accident] ON [Fuel Pit
Report].[Report Month] = [A/C Accident].[Report Month]) INNER JOIN (([Runway
Totals] INNER JOIN [SAR Missions] ON [Runway Totals].[Report Month] = [SAR
Missions].[Report Month]) INNER JOIN [SAR Sortie Totals] ON [SAR
Missions].[Report Month] = [SAR Sortie Totals].[Report Month]) ON [A/C
Accident].[Report Month] = [Runway Totals].[Report Month]) INNER JOIN
((([Field Hours] INNER JOIN Airfield ON [Field Hours].[Report Month] =
Airfield.[Report Month]) INNER JOIN [Individual Unit] ON Airfield.[Report
Month] = [Individual Unit].[Report Month]) INNER JOIN ((((([Aircraft
Transfers] INNER JOIN [Airspace Totals] ON [Aircraft Transfers].[Report
Month] = [Airspace Totals].[Report Month]) INNER JOIN [CAG/SFARP] ON
[Airspace Totals].[Report Month] = [CAG/SFARP].[Report Month]) INNER JOIN
[CNI Ashore] ON [CAG/SFARP].[Report Month] = [CNI Ashore].[Report Month])
INNER JOIN [Community Service] ON [CNI Ashore].[Report Month] = [Community
Service].[Report Month]) INNER JOIN [Crewmember Hours] ON [Community
Service].[Report Month] = [Crewmember Hours].[Report Month]) ON [Individual
Unit].[Report Month] = [Crewmember Hours].[Report Month]) ON [SAR Sortie
Totals].[Report Month] = [Field Hours].[Report Month]) INNER JOIN ([Air
Terminal Processing] INNER JOIN Arrestments ON [Air Terminal
Processing].[Report Month] = Arrestments.[Report Month]) ON [Fuel Pit
Report].[Report Month] = [Air Terminal Processing].[Report Month]
WHERE ((([Aircraft Transfers].[Report Month])=[Month]) AND (([Airspace
Totals].[Report Month])=[Month]) AND (([CAG/SFARP].[Report Month])=[Month])
AND (([CNI Ashore].[Report Month])=[Month]) AND (([Community Service].[Report
Month])=[Month]) AND (([Crewmember Hours].[Report Month])=[Month]) AND
(([Individual Unit].[Report Month])=[Month]) AND ((Airfield.[Report
Month])=[Month]) AND (([Field Hours].[Report Month])=[Month]) AND (([SAR
Sortie Totals].[Report Month])=[Month]) AND (([SAR Missions].[Report
Month])=[Month]) AND (([Runway Totals].[Report Month])=[Month]) AND (([A/C
Accident].[Report Month])=[Month]) AND (([Fuel Pit Report].[Report
Month])=[Month]) AND (([Air Terminal Processing].[Report Month])=[Month]) AND
((Arrestments.[Report Month])=[Month]));

Can you post your SQL?

Does the criteria in the query for the date look like

Forms![FormName]![ComboName]

--
Good Luck
BS"D

I am trying to create a parameter query where the info to be looked up was
entered via a combo box (i.e. Month, Year). Just a little insight: I am
creating a year end report and put in a combo box for the month so that info
in that column will not be entered wrong and therefore doesn't show up. When
I try to run a parameter query for the month nothing shows up.

 

[MTstraw]

I now get the following error: The specified field [Report Month] could
refoer to more thean one table listed in the from clause of your SQL
statement.

From your query I can see that you linked all the tables using the month
field, so there is no need to create a criteria under each month field,
filter on one and the join will take care of the rest.

The criteria [Month] need to be a path to the form

Forms![FormName]![ComboName]

Or, base the report on the full SQL with no criteria, and then open the
report using the WhereCondition of the OpenReport Command line

Docmd.OpenReport "ReportName" , , , "[Aircraft Transfers].[Report Month]=" & Me.[ComboName]

--
Good Luck
BS"D

SELECT [Aircraft Transfers].*, [Airspace Totals].*, [CAG/SFARP].*, [CNI
Ashore].*, [Community Service].*, [Crewmember Hours].*, [Fuel Pit Report].*,
[Individual Unit].*, [Runway Totals].*, [SAR Missions].*, [SAR Sortie
Totals].*, [A/C Accident].*, [Field Hours].*, [Air Terminal Processing].*,
Airfield.*, Arrestments.*
FROM ((([Fuel Pit Report] INNER JOIN [A/C Accident] ON [Fuel Pit
Report].[Report Month] = [A/C Accident].[Report Month]) INNER JOIN (([Runway
Totals] INNER JOIN [SAR Missions] ON [Runway Totals].[Report Month] = [SAR
Missions].[Report Month]) INNER JOIN [SAR Sortie Totals] ON [SAR
Missions].[Report Month] = [SAR Sortie Totals].[Report Month]) ON [A/C
Accident].[Report Month] = [Runway Totals].[Report Month]) INNER JOIN
((([Field Hours] INNER JOIN Airfield ON [Field Hours].[Report Month] =
Airfield.[Report Month]) INNER JOIN [Individual Unit] ON Airfield.[Report
Month] = [Individual Unit].[Report Month]) INNER JOIN ((((([Aircraft
Transfers] INNER JOIN [Airspace Totals] ON [Aircraft Transfers].[Report
Month] = [Airspace Totals].[Report Month]) INNER JOIN [CAG/SFARP] ON
[Airspace Totals].[Report Month] = [CAG/SFARP].[Report Month]) INNER JOIN
[CNI Ashore] ON [CAG/SFARP].[Report Month] = [CNI Ashore].[Report Month])
INNER JOIN [Community Service] ON [CNI Ashore].[Report Month] = [Community
Service].[Report Month]) INNER JOIN [Crewmember Hours] ON [Community
Service].[Report Month] = [Crewmember Hours].[Report Month]) ON [Individual
Unit].[Report Month] = [Crewmember Hours].[Report Month]) ON [SAR Sortie
Totals].[Report Month] = [Field Hours].[Report Month]) INNER JOIN ([Air
Terminal Processing] INNER JOIN Arrestments ON [Air Terminal
Processing].[Report Month] = Arrestments.[Report Month]) ON [Fuel Pit
Report].[Report Month] = [Air Terminal Processing].[Report Month]
WHERE ((([Aircraft Transfers].[Report Month])=[Month]) AND (([Airspace
Totals].[Report Month])=[Month]) AND (([CAG/SFARP].[Report Month])=[Month])
AND (([CNI Ashore].[Report Month])=[Month]) AND (([Community Service].[Report
Month])=[Month]) AND (([Crewmember Hours].[Report Month])=[Month]) AND
(([Individual Unit].[Report Month])=[Month]) AND ((Airfield.[Report
Month])=[Month]) AND (([Field Hours].[Report Month])=[Month]) AND (([SAR
Sortie Totals].[Report Month])=[Month]) AND (([SAR Missions].[Report
Month])=[Month]) AND (([Runway Totals].[Report Month])=[Month]) AND (([A/C
Accident].[Report Month])=[Month]) AND (([Fuel Pit Report].[Report
Month])=[Month]) AND (([Air Terminal Processing].[Report Month])=[Month]) AND
((Arrestments.[Report Month])=[Month]));

Can you post your SQL?

Does the criteria in the query for the date look like

Forms![FormName]![ComboName]

--
Good Luck
BS"D


:

I am trying to create a parameter query where the info to be looked up was
entered via a combo box (i.e. Month, Year). Just a little insight: I am
creating a year end report and put in a combo box for the month so that info
in that column will not be entered wrong and therefore doesn't show up. When
I try to run a parameter query for the month nothing shows up.