CODEWITHSUNDEEP
sqlms-accessms-access-2013
Teamgilla
Teamgilla

Reputation: 69

Trying to Get SELECT TOP to work with Parameter in ACCESS

This is building on some code I got the other day (thanks to peterm). I am now trying to select the TOP X number of results after calculations on the query. The X can range from 1 to 8 depending on the number of results per player.

This is the code I have but I get a syntax error when I try to run it.

SELECT
  PlayerID
, RoundID
, PlayedTo
, (SELECT Count(PlayerID) FROM PlayedToCalcs) AS C
, iif(
    C <= 6
  , 1
  , iif(
      C <= 8
    , 2
    , (
        iif(
          C <= 10
        , 3
        , (
            iif(
              C <= 12
            , 4
            , (
                iif(
                  C <= 14
                , 5
                , (
                    iif(
                      C <= 16
                    , 6
                    , (
                        iif(
                          C <= 18
                        , 7
                        , (iif(C <= 20, 8, 999))
                        )
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  ) AS X
FROM PlayedToCalcs AS s
WHERE PlayedTo IN (
  SELECT TOP (X) PlayedTo
  FROM PlayedToCalcs
  WHERE PlayerID = s.PlayerID
  ORDER BY PlayedTo DESC, RoundID DESC
)
ORDER BY PlayerID, PlayedTo DESC, RoundID DESC;

Here is a link http://sqlfiddle.com/#!3/a726c/4 with a small sample of the data I'm trying to use it on.

Upvotes: 1

Views: 1513

Answers (2)

HansUp
HansUp

Reputation: 97100

The Access db engine does not allow you to use a parameter for SELECT TOP. You must include a literal value in the SQL statement.

For example this query works correctly.

SELECT TOP 2 *
FROM tblFoo
ORDER BY id DESC;

But attempting to substitute a parameter, how_many, triggers error 3141, "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

SELECT TOP how_many *
FROM tblFoo
ORDER BY id DESC;

Upvotes: 3

Pankaj Jaju
Pankaj Jaju

Reputation: 5471

The reason being in SQL Server (the simulator you used in SQL Fiddle), you cannot use IIF. Try using CASE.

And there is a limitation of using 7 nested IIF in Access.

Upvotes: 0

Related Questions