Not your typical "too few parameters..." error

[Trey Guy]

I have an ASP page I use as a front end to an Access db. I am running a
query from that page that is returning a "Too few parameters..." error. The
query works fine in Access. I have checked all field names and they are
correct. The SQL is below. Any ideas as to the cause of this error?

Thanks,
Trey

SELECT qryParticipantOutcomesAggregate.IndID, Indicators.IndName,
Sum(qryParticipantOutcomesAggregate.NEligible) AS SumOfNEligible,
Sum(qryParticipantOutcomesAggregate.NAchOut) AS SumOfNAchOut,
[SumOfNAchOut]/[SumOfNEligible] AS PAchOut, Indicators.Target FROM
Indicators RIGHT JOIN qryParticipantOutcomesAggregate ON Indicators.IndID =
qryParticipantOutcomesAggregate.IndID WHERE ((ProgID=17) And
((qryParticipantOutcomesAggregate.AggMonth) Between #01/01/1995# And
#01/01/2010#)) GROUP BY qryParticipantOutcomesAggregate.IndID,
Indicators.IndName, Indicators.Target;

 

[Bob Barrows]

[SumOfNAchOut]/[SumOfNEligible] AS PAchOut,

I don't think you can refer to aliases within the same SELECT
statement. You will likely have to run a nested query...

That was my first thought as well, but this works fine in Jet, even when
called via ADO (I just tried it).

Trey, how are you running this query? Dynamic SQL? Saved Query?

Bob Barrows

 

[Trey Guy]

Thanks for all the feedback. It occurred to me that I've had this problem
before, so I went back to my notes. It seems that the use of aliases is the
problem. Nested query works fine.

Thanks again,
Trey

[SumOfNAchOut]/[SumOfNEligible] AS PAchOut,

I don't think you can refer to aliases within the same SELECT
statement. You will likely have to run a nested query...

That was my first thought as well, but this works fine in Jet, even when
called via ADO (I just tried it).

Trey, how are you running this query? Dynamic SQL? Saved Query?

Bob Barrows

 

[Bob Barrows]

Really? Here is what I tried:

Table1 has a Text column called FK, containing a few rows of garbage sample
data (the first row contains merely the letter A).

strsql = "SELECT [FK] & 't' AS Result, [Result] & '2' AS REsult2 FROM
Table1"
Set rs = cn.Execute(strsql, , adCmdText)
response.write rs.GetString(adClipString, 1, ";")

It worked fine:

At;At2

I also tried it with a saved query: no problem.

Oh well, I'm happy you solved your problem.

Bob

 

[Mark Schupp]

SQLServer uses single quotes around dates, not "#".

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
(e-mail address removed)
http://www.ielearning.com
714.637.9480 x17

 

[Mark Schupp]

oops, misread the question.

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
(e-mail address removed)
http://www.ielearning.com
714.637.9480 x17

SQLServer uses single quotes around dates, not "#".

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
(e-mail address removed)
http://www.ielearning.com
714.637.9480 x17

I have an ASP page I use as a front end to an Access db. I am running a
query from that page that is returning a "Too few parameters..." error. The
query works fine in Access. I have checked all field names and they are
correct. The SQL is below. Any ideas as to the cause of this error?

Thanks,
Trey

SELECT qryParticipantOutcomesAggregate.IndID, Indicators.IndName,
Sum(qryParticipantOutcomesAggregate.NEligible) AS SumOfNEligible,
Sum(qryParticipantOutcomesAggregate.NAchOut) AS SumOfNAchOut,
[SumOfNAchOut]/[SumOfNEligible] AS PAchOut, Indicators.Target FROM
Indicators RIGHT JOIN qryParticipantOutcomesAggregate ON

Indicators.IndID

=
qryParticipantOutcomesAggregate.IndID WHERE ((ProgID=17) And
((qryParticipantOutcomesAggregate.AggMonth) Between #01/01/1995# And
#01/01/2010#)) GROUP BY qryParticipantOutcomesAggregate.IndID,
Indicators.IndName, Indicators.Target;