Parameters in a union query (1 Viewer)

[accessaspire219]

I have a union query the SQL for which is as given below:

TABLE MIOH_MONTH UNION TABLE SERVICE_MONTH UNION TABLE SURPLUS_MONTH UNION TABLE TRFALL_MONTH;

One of the columns of the union query is a month column, I want to add a parameter to this query which will prompt the user to enter the month that he wants to view. Normally I would have done this in this design view (in the parameter row) however I cannot do so here as this is a union query. Any idea on how I could specify parameters in this query?
Thanks.

 

[pbaldy]

That doesn't run like that, does it? The proper format would be

SELECT ...
FROM...
WHERE FieldName = [Enter month]
UNION ALL
SELECT ...
FROM...
WHERE FieldName = [Enter month]

 

[accessaspire219]

I will try it out this way. It does work with the format I have posted - I use the posted format because it is easier when working with more than two queries. Like in this case I have 4 queries that would be unified into 1 query.

 

[pbaldy]

Interesting; I've never seen that format before, but it does work. I suspect to add a parameter you'll have to go to the SELECT format, but since I've never seen the other format I could be wrong.

 

[accessaspire219]

This is what I have,

SELECT SUPERVISOR, NAME, SCALENAME, SumOfINVDOLR, SumOfCONSDOLR, METRIC, PERIOD1, SCALEMIN, SCALEMID, SCALEMAX 
FROM MIOH_MONTH
WHERE PERIOD1 = Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate]
UNION ALL
SELECT SUPERVISOR, NAME, SCALENAME,  SumOfSALES, SumOfBOS, METRIC, PERIOD1, SCALEMIN, SCALEMID, SCALEMAX
FROM SERVICE_MONTH
WHERE PERIOD1 = Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate]
UNION ALL
SELECT SUPERVISOR, NAME, SCALENAME, SumOfSURPDOLL, DUMMY, METRIC, PERIOD1, SCALEMIN, SCALEMID, SCALEMAX
FROM SURPLUS_MONTH
WHERE PERIOD1 = Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate]
UNION ALL SELECT SUPERVISOR, NAME, SCALENAME, SumOfCHARGES, SumOfCONSDOLR, METRIC, PERIOD1, SCALEMIN, SCALEMID, SCALEMAX
FROM TRFALL_MONTH
WHERE PERIOD1 = Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate];

I am getting an error message saying "Syntax error (missing operator) in query expression 'PERIOD1 = Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate]'
What am I doing wrong?

 

[Banana]

You don't need a = for the BETWEEN. Just "Period1 BETWEEN .... AND .." (e.g. delete the = from the statement)


FWIW, I've never seen that format from OP; good to know about new formats. I suspect it's just like other times where we can select the tables as recordsource but have no control over which columns makes into the form unless we use SELECT format.

 

[accessaspire219]

Yeah that's kind of what it is. I use it when I have many queries to unify or when I want all columns. Saves the time of selecting each column.
It worked btw - I took out the "="
Thanks!