SQL false Enter Parameter prompt - need help

  • Thread starter Thread starter G Lykos
  • Start date Start date
G

G Lykos

The following SQL code was generated by the Access 2003 query grid. It
performs a pseudo-union via an expression.

When executed, the Enter Parameter prompt pops up for TH and EH. I <enter>
with no data in response to each prompt, and the query then executes
correctly (suggesting that whatever Access thought it didn't understand upon
initial parsing, it figured out during processing). Can the Access SQL code
below be modified to get rid of the false prompts?

SELECT [TS].ID AS [SL], [TS].Task, [TS].UID,
Round(IIf([TH]>0,[EH]/[TH],0),3) AS [PC], Sum(nz([Dev TH],0)+nz([Test
TH],0)) AS [TH], Sum(nz([Dev EH],0)+nz([Test EH],0)) AS [EH]
FROM ([TS] LEFT JOIN [FDPC] ON [TS].UID = FDPC.pjUID) LEFT JOIN [FTPC] ON
[TS].UID = FTPC.pjUID
GROUP BY [TS].ID, [TS].Task, [TS].UID, Round(IIf([TH]>0,[EH]/[TH],0),3),
[TS].[EVPS]
HAVING ((([TS].[EVPS])="Tracking.xls"));

Thanks,
George
 
When executed, the Enter Parameter prompt pops up for TH and EH. I <enter>
with no data in response to each prompt, and the query then executes
correctly (suggesting that whatever Access thought it didn't understand upon
initial parsing, it figured out during processing). Can the Access SQL code
below be modified to get rid of the false prompts?
Click to expand...

You often cannot reuse calculated fields in further expressions. Try
recapitulating the calculation in expressions using TH and EH. If
necessary you might want to use a temp table to store these values.

John W. Vinson[MVP]
 
Back
Top