CODEWITHSUNDEEP
sqlms-access
user1342164
user1342164

Reputation: 1454

No value given for the required parameter

I am trying to run the qry below against an access database and I get an error No value Given for the required parameter?

SELECT        ID, DateColumn, Less90, Between90180, Between180365, GreaterThan365, SUM(Less90) + SUM(Between90180) + SUM(Between180365) + SUM(GreaterThan365) 
                     AS Total, SUM(Between180365) / Total AS Expr1,
                         (SELECT        SUM(Between180365) / (SUM(Less90) + SUM(Between90180) + SUM(Between180365) + SUM(GreaterThan365))
                           FROM            tblHandpieceFaliuresAge T2
                           WHERE        T2.ID <= tblHandpieceFaliuresAge.ID) AS RunningSum
FROM            tblHandpieceFaliuresAge
GROUP BY ID, DateColumn, Less90, Between90180, Between180365, GreaterThan365

Upvotes: 2

Views: 9180

Answers (2)

Heinzi
Heinzi

Reputation: 172270

The usual cause for this is that you have misspelled one of the field names, and, thus, Access thinks it is a parameter (which is not specified).

There are two ways to fix this:

  • Manually check evey field name, to make sure it is spelled correctly or
  • start removing fields from your query until the problem disappears. The last field removed is the culprit.

Upvotes: 3

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

You have typed one of your columns incorrectly, and Access thinks you are trying to pass a parameter.

Go over your field names again and make sure they have all been entered correctly.

I believe the problem may be this:

SUM(Less90) + SUM(Between90180) + SUM(Between180365) + SUM(GreaterThan365) 
                 AS Total

And then you refer to it later as Total here:

SUM(Between180365) / Total

Access can't take the alias and re-use it in the query, you need this:

SUM(Between180365) / 
    (SUM(Less90) + SUM(Between90180) + SUM(Between180365) + SUM(GreaterThan365))

Also make sure you handle the denominator so you don't divide by zero.

Upvotes: 7

Related Questions