Enter Parameter Value in calculation

  • Thread starter Thread starter Octet32
  • Start date Start date
O

Octet32

I have A query that has Calculation Table1 – Table2 = Table3
when I run the query a box opens saying Enter Parameter Value (named
Table1) I click ok then a box opens saying Enter Parameter Value (named
Table2) I click ok
everything looks ok when the query runs,
Why is this doing this how can I stop this?
Thank
Octet
 
Is that the exact calculation? I don't see how that would work, but in any
case it seems Table1 and Table2 are not fields in the query.
 
You can not subtract one table from another. You can subtract a field in
one table from a field in another.
The reason that it is saying Enter Parameter Value is because it does not
recognize Table1 as a valid data source.
Post the SQL statement of your query. Open the query in design view, click
on menu VIEW - SQL View, highlight all in the window, copy and paste in a
post.
 
Sorry rookie mistake its Field1 and Field2 not Table

SELECT [1_Total].DATE, Sum([1_Total].SumOfField1) AS SumOfSumOfField1,
Sum([1_Total].[Field2 Total]) AS [SumOfField2 Total], [SumOfField2
Total]-[SumOfSumOfField1] AS Variance
FROM 1_Total
GROUP BY [1_Total].DATE, [SumOfField2 Total]-[SumOfSumOfField1];
 
You need to repeat the calculation. You cannot refer TO the alias for the
calculation - especially in a WHERE or GROUP BY clause. You may be able to do
so in the SELECT clause.

So
[SumOfField2 Total]-[SumOfSumOfField1]
should become
Sum([1_Total].[Field2 Total])-Sum([1_Total].SumOfField1)

SELECT [1_Total].DATE
, Sum([1_Total].SumOfField1) AS SumOfSumOfField1
, Sum([1_Total].[Field2 Total]) AS [SumOfField2 Total]
, Sum([1_Total].[Field2 Total])-Sum([1_Total].SumOfField1) AS Variance
FROM 1_Total
GROUP BY [1_Total].DATE
, Sum([1_Total].[Field2 Total])-Sum([1_Total].SumOfField1);

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Sorry rookie mistake its Field1 and Field2 not Table

SELECT [1_Total].DATE, Sum([1_Total].SumOfField1) AS SumOfSumOfField1,
Sum([1_Total].[Field2 Total]) AS [SumOfField2 Total], [SumOfField2
Total]-[SumOfSumOfField1] AS Variance
FROM 1_Total
GROUP BY [1_Total].DATE, [SumOfField2 Total]-[SumOfSumOfField1];


KARL DEWEY said:
You can not subtract one table from another. You can subtract a field in
one table from a field in another.
The reason that it is saying Enter Parameter Value is because it does not
recognize Table1 as a valid data source.
Post the SQL statement of your query. Open the query in design view, click
on menu VIEW - SQL View, highlight all in the window, copy and paste in a
post.
Click to expand...
Click to expand...
 
Back
Top