Unwanted parameter prompt

A

Ann Scharpf

Hi,

I have a query that is prompting me for a parameter when I run it. I can
just click OK and the query calculates the correct results.

Here is the SQL code for my query.

SELECT SchedulingFundedHrs.VendorName, SchedulingCosts1.Project,
SchedulingCosts1.Vendor, Sum(SchedulingCosts1.TotalHours) AS SumOfTotalHours,
Sum(SchedulingCosts1.Cost) AS CostToDate,
SchedulingFundedHrs.FYDollarsFunded, [FYDollarsFunded]-[CostToDate] AS
AmtRemaining
FROM SchedulingCosts1 LEFT JOIN SchedulingFundedHrs ON
SchedulingCosts1.Project = SchedulingFundedHrs.ProjectCode
GROUP BY SchedulingFundedHrs.VendorName, SchedulingCosts1.Project,
SchedulingCosts1.Vendor, SchedulingFundedHrs.FYDollarsFunded,
[FYDollarsFunded]-[CostToDate];

I did see a post from last May by Klatuu about adding the alias for the
expression to the end of the SQL. I tried adding "AS AmtRemaining" to the
very end of my SQL and got the following error:

Syntax error (missing operator) in query expression
'[FYDollarsFunded]-[CostToDate] AS AmtRemaining'.

Can anyone please tell me how to get Access to stop prompting for the
CostToDate?

Thanks.
 
J

Jason Lepack

Because you're calculating costToDate in this query, it's not a field
Name yet. Access looks at all the fields in the tables that you've
given it and doesn't see CostToDate.
 
A

Ann Scharpf

I thought that might be the case but I have ANOTHER query in this database
where I use two values that are calculated in the query itself. This query
does NOT display a parameter prompt when you run it. I don't understand what
is different between the two. Here is the second query. Can you please
explain why they act differently?

SELECT [--TotalWorkHoursByProject].Project,
[--TotalWorkHoursByProject].Vendor, AGoalVendorFundedHrs.FYHrsFunded,
[--TotalWorkHoursByProject].SumOfTotalHours,
(26-[RemainingPPEs])*[PPEHrsFunded] AS ExpectedHrsToDate,
[ExpectedHrsToDate]-[SumOfTotalHours] AS VarianceToDate,
IIf([VarianceToDate]<1,"OverExpended") AS OverUnder
FROM [--TotalWorkHoursByProject] INNER JOIN AGoalVendorFundedHrs ON
([--TotalWorkHoursByProject].Vendor = AGoalVendorFundedHrs.Vendor) AND
([--TotalWorkHoursByProject].Project = AGoalVendorFundedHrs.ProjectCode),
LastPPEDateOnEbizFile INNER JOIN PPE_Dates ON
LastPPEDateOnEbizFile.LastPPEDate = PPE_Dates.PPEdate
ORDER BY [--TotalWorkHoursByProject].Project;

--
Ann Scharpf


Jason Lepack said:
Because you're calculating costToDate in this query, it's not a field
Name yet. Access looks at all the fields in the tables that you've
given it and doesn't see CostToDate.

Hi,

I have a query that is prompting me for a parameter when I run it. I can
just click OK and the query calculates the correct results.

Here is the SQL code for my query.

SELECT SchedulingFundedHrs.VendorName, SchedulingCosts1.Project,
SchedulingCosts1.Vendor, Sum(SchedulingCosts1.TotalHours) AS SumOfTotalHours,
Sum(SchedulingCosts1.Cost) AS CostToDate,
SchedulingFundedHrs.FYDollarsFunded, [FYDollarsFunded]-[CostToDate] AS
AmtRemaining
FROM SchedulingCosts1 LEFT JOIN SchedulingFundedHrs ON
SchedulingCosts1.Project = SchedulingFundedHrs.ProjectCode
GROUP BY SchedulingFundedHrs.VendorName, SchedulingCosts1.Project,
SchedulingCosts1.Vendor, SchedulingFundedHrs.FYDollarsFunded,
[FYDollarsFunded]-[CostToDate];

I did see a post from last May by Klatuu about adding the alias for the
expression to the end of the SQL. I tried adding "AS AmtRemaining" to the
very end of my SQL and got the following error:

Syntax error (missing operator) in query expression
'[FYDollarsFunded]-[CostToDate] AS AmtRemaining'.

Can anyone please tell me how to get Access to stop prompting for the
CostToDate?

Thanks.
Click to expand...
Click to expand...
 
J

Jason Lepack

Sorry, I should have specified that you can't use a field name that
you are calculating in the WHERE or GROUP BY clause.

If I have a table:

table1:
field1 - number
field2 - number

table1 - data:
field1, field2
1,2
1,2
7,8
7,8

Note that this works fine:
SELECT Table1.field1,
Table1.field2,
[field1] + [field2] AS Test1,
[test1] + [field1] AS Test2,
[field2] + [test1] AS test3,
[test2] + [test3] AS test4
FROM Table1;

This also works:
SELECT Table1.field1,
Table1.field2,
[field1] + [field2] AS Test1
FROM Table1
GROUP BY Table1.field1,Table1.field2,[field1] + [field2];

But this will not, it will ask for you to enter Test1:
SELECT Table1.field1,
Table1.field2,
[field1] + [field2] AS Test1
FROM Table1
GROUP BY Table1.field1,Table1.field2,Test1;

I hope that now, you understand what the problem is. If you don't
just post back and we'll try again.

Cheers,
Jason Lepack

Now in query 2 all that you are doing is calculating
I thought that might be the case but I have ANOTHER query in this database
where I use two values that are calculated in the query itself. This query
does NOT display a parameter prompt when you run it. I don't understand what
is different between the two. Here is the second query. Can you please
explain why they act differently?

SELECT [--TotalWorkHoursByProject].Project,
[--TotalWorkHoursByProject].Vendor, AGoalVendorFundedHrs.FYHrsFunded,
[--TotalWorkHoursByProject].SumOfTotalHours,
(26-[RemainingPPEs])*[PPEHrsFunded] AS ExpectedHrsToDate,
[ExpectedHrsToDate]-[SumOfTotalHours] AS VarianceToDate,
IIf([VarianceToDate]<1,"OverExpended") AS OverUnder
FROM [--TotalWorkHoursByProject] INNER JOIN AGoalVendorFundedHrs ON
([--TotalWorkHoursByProject].Vendor = AGoalVendorFundedHrs.Vendor) AND
([--TotalWorkHoursByProject].Project = AGoalVendorFundedHrs.ProjectCode),
LastPPEDateOnEbizFile INNER JOIN PPE_Dates ON
LastPPEDateOnEbizFile.LastPPEDate = PPE_Dates.PPEdate
ORDER BY [--TotalWorkHoursByProject].Project;

--
Ann Scharpf



Jason Lepack said:
Because you're calculating costToDate in this query, it's not a field
Name yet. Access looks at all the fields in the tables that you've
given it and doesn't see CostToDate.
Click to expand...
Hi,
I have a query that is prompting me for a parameter when I run it. I can
just click OK and the query calculates the correct results.
Here is the SQL code for my query.
SELECT SchedulingFundedHrs.VendorName, SchedulingCosts1.Project,
SchedulingCosts1.Vendor, Sum(SchedulingCosts1.TotalHours) AS SumOfTotalHours,
Sum(SchedulingCosts1.Cost) AS CostToDate,
SchedulingFundedHrs.FYDollarsFunded, [FYDollarsFunded]-[CostToDate] AS
AmtRemaining
FROM SchedulingCosts1 LEFT JOIN SchedulingFundedHrs ON
SchedulingCosts1.Project = SchedulingFundedHrs.ProjectCode
GROUP BY SchedulingFundedHrs.VendorName, SchedulingCosts1.Project,
SchedulingCosts1.Vendor, SchedulingFundedHrs.FYDollarsFunded,
[FYDollarsFunded]-[CostToDate];
I did see a post from last May by Klatuu about adding the alias for the
expression to the end of the SQL. I tried adding "AS AmtRemaining" to the
very end of my SQL and got the following error:
Syntax error (missing operator) in query expression
'[FYDollarsFunded]-[CostToDate] AS AmtRemaining'.
Can anyone please tell me how to get Access to stop prompting for the
CostToDate?
Thanks.
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
A

Ann Scharpf

Thanks for taking the time to write such a complete explanation, Jason. Yes,
this clears up my question.
--
Ann Scharpf


Jason Lepack said:
Sorry, I should have specified that you can't use a field name that
you are calculating in the WHERE or GROUP BY clause.

If I have a table:

table1:
field1 - number
field2 - number

table1 - data:
field1, field2
1,2
1,2
7,8
7,8

Note that this works fine:
SELECT Table1.field1,
Table1.field2,
[field1] + [field2] AS Test1,
[test1] + [field1] AS Test2,
[field2] + [test1] AS test3,
[test2] + [test3] AS test4
FROM Table1;

This also works:
SELECT Table1.field1,
Table1.field2,
[field1] + [field2] AS Test1
FROM Table1
GROUP BY Table1.field1,Table1.field2,[field1] + [field2];

But this will not, it will ask for you to enter Test1:
SELECT Table1.field1,
Table1.field2,
[field1] + [field2] AS Test1
FROM Table1
GROUP BY Table1.field1,Table1.field2,Test1;

I hope that now, you understand what the problem is. If you don't
just post back and we'll try again.

Cheers,
Jason Lepack

Now in query 2 all that you are doing is calculating
I thought that might be the case but I have ANOTHER query in this database
where I use two values that are calculated in the query itself. This query
does NOT display a parameter prompt when you run it. I don't understand what
is different between the two. Here is the second query. Can you please
explain why they act differently?

SELECT [--TotalWorkHoursByProject].Project,
[--TotalWorkHoursByProject].Vendor, AGoalVendorFundedHrs.FYHrsFunded,
[--TotalWorkHoursByProject].SumOfTotalHours,
(26-[RemainingPPEs])*[PPEHrsFunded] AS ExpectedHrsToDate,
[ExpectedHrsToDate]-[SumOfTotalHours] AS VarianceToDate,
IIf([VarianceToDate]<1,"OverExpended") AS OverUnder
FROM [--TotalWorkHoursByProject] INNER JOIN AGoalVendorFundedHrs ON
([--TotalWorkHoursByProject].Vendor = AGoalVendorFundedHrs.Vendor) AND
([--TotalWorkHoursByProject].Project = AGoalVendorFundedHrs.ProjectCode),
LastPPEDateOnEbizFile INNER JOIN PPE_Dates ON
LastPPEDateOnEbizFile.LastPPEDate = PPE_Dates.PPEdate
ORDER BY [--TotalWorkHoursByProject].Project;

--
Ann Scharpf



Jason Lepack said:
Because you're calculating costToDate in this query, it's not a field
Name yet. Access looks at all the fields in the tables that you've
given it and doesn't see CostToDate.
Click to expand...
On Mar 12, 3:33 pm, Ann Scharpf <[email protected]>
wrote:
Hi,
Click to expand...
I have a query that is prompting me for a parameter when I run it. I can
just click OK and the query calculates the correct results.
Click to expand...
Here is the SQL code for my query.
Click to expand...
SELECT SchedulingFundedHrs.VendorName, SchedulingCosts1.Project,
SchedulingCosts1.Vendor, Sum(SchedulingCosts1.TotalHours) AS SumOfTotalHours,
Sum(SchedulingCosts1.Cost) AS CostToDate,
SchedulingFundedHrs.FYDollarsFunded, [FYDollarsFunded]-[CostToDate] AS
AmtRemaining
FROM SchedulingCosts1 LEFT JOIN SchedulingFundedHrs ON
SchedulingCosts1.Project = SchedulingFundedHrs.ProjectCode
GROUP BY SchedulingFundedHrs.VendorName, SchedulingCosts1.Project,
SchedulingCosts1.Vendor, SchedulingFundedHrs.FYDollarsFunded,
[FYDollarsFunded]-[CostToDate];
Click to expand...
I did see a post from last May by Klatuu about adding the alias for the
expression to the end of the SQL. I tried adding "AS AmtRemaining" to the
very end of my SQL and got the following error:
Click to expand...
Syntax error (missing operator) in query expression
'[FYDollarsFunded]-[CostToDate] AS AmtRemaining'.
Click to expand...
Can anyone please tell me how to get Access to stop prompting for the
CostToDate?
Click to expand...
Thanks.
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error - CANNOT HAVE AGGREGATE FUNCTION IN EXPRESSION 4
Parameter Query with DSum 2
Forcing an ambiguous outer join 6
Unwanted Parameter Prompt 4
Create a Query that Prompts for Date and Time 4
Condition and equation problems 4
Expression Error in a Parameter Query 9
SQL false Enter Parameter prompt - need help 1

Top