Enter Parameter Value prompt in error

[FishCop]

Hello all,

I have put together a query which has several expressions in which are subsequently used, within the same query, to make another expression.

The query runs fine and gives correct calculations however, it prompts the user to enter a parameter value for the expressions used to build the final expression.

e.g.
Expr1 Expr2 Expr3
x - y x - z (Expr1+Expr2)/2

It asks for Expr1 and Expr2... but on clicking 'OK' without entering any values it comes up with the correct values.

Is this just a quirk of Access - Is there a way to simply stop the 'Enter Parameter Value' prompt from coming up (im thinking like in vb - on error go to next)

OR does anyone know why Access does this and how i can 'fix' it.

All the best,

Luke

 

[pr2-eugin]

Show the complete code. Sounds like you have used the Expression as a part of a GROUP BY clause. In the Totals column change the Expr3 to Expression instead of Group By.

 

[FishCop]

I am indeed using a GroupBy cluase - how did you know that! mental. Anyway heres the SQL - is that what you meant? By the way i didnt write this - im using the query builder.


SELECT SumOfficerObsQ.FshID, Sum(SumOfficerObsQ.SumOfLandedWeight) AS SumOfSumOfLandedWeight, Sum(SumProcessorQ.SumOfLandedWeight) AS SumOfSumOfLandedWeight1, Sum(SumReturnsQ.SumOfLandedWeight) AS SumOfSumOfLandedWeight2, FisheryT.FshTAC, [FshTAC]-[SumOfSumOfLandedWeight] AS RemTACOffObs, [FshTAC]-[SumOfSumOfLandedWeight1] AS RemTACProcessors, [FshTAC]-[SumOfSumOfLandedWeight2] AS RemTACReturns, ([SumOfSumOfLandedWeight]+[SumOfSumOfLandedWeight1]+[SumOfSumOfLandedWeight2])/3 AS TACMean, [FshTAC]-[TACMean] AS RemTACMean
FROM ((SumOfficerObsQ INNER JOIN SumProcessorQ ON SumOfficerObsQ.FshID = SumProcessorQ.FshID) INNER JOIN SumReturnsQ ON SumProcessorQ.FshID = SumReturnsQ.FshID) INNER JOIN FisheryT ON SumOfficerObsQ.FshID = FisheryT.FshID
GROUP BY SumOfficerObsQ.FshID, FisheryT.FshTAC, [FshTAC]-[SumOfSumOfLandedWeight], [FshTAC]-[SumOfSumOfLandedWeight1], [FshTAC]-[SumOfSumOfLandedWeight2], ([SumOfSumOfLandedWeight]+[SumOfSumOfLandedWeight1]+[SumOfSumOfLandedWeight2])/3, [FshTAC]-[TACMean];

 

[FishCop]

Genius - it worked when i changed the columns to Expression

Cheers again!

Luke

 

[pr2-eugin]

I knew you can work it out ! Good luck.