CODEWITHSUNDEEP
sqlms-accessoledb
Timothy
Timothy

Reputation: 383

WHERE clause has a valid invalid parameter

I have a cross-tab query, as shown below:

TRANSFORM Sum([Raw Scores].[Raw Score]) AS SumOfScore
SELECT [Raw Scores].PlayerID, [Raw Scores].[First Name], [Raw Scores].[Last Name], [Raw Scores].[Round Number], Sum([Raw Scores].Score) AS [Final Score], Sum([Raw Scores].[Raw Score]) AS [Total Shots]
FROM [Raw Scores]
WHERE [Round Number] = [Enter Round Number:]
GROUP BY [Raw Scores].PlayerID, [Raw Scores].[First Name], [Raw Scores].[Last Name], [Raw Scores].[Round Number]
PIVOT [Raw Scores].[Hole ID];

[Raw Scores].[Round Number] is a column in another table, which has data in it.
this.[Enter Round Number:] however, is a parameter.

The Problem:

What I'm trying to do is sort all the scores by the round that they were aquired in, however, the WHERE clause seem to have a problem.
Whenever a non-column name is used, it spits out the following error:

"The Microsoft Access database engine does not recognize '[Enter Round Number:]' as a valid field name or expression."

But, when a definite value is used instead (one written in the query rather than a parameter) is used, the query runs perfectly fine.

TRANSFORM Sum([Raw Scores].[Raw Score]) AS SumOfScore
SELECT [Raw Scores].PlayerID, [Raw Scores].[First Name], [Raw Scores].[Last Name], [Raw Scores].[Round Number], Sum([Raw Scores].Score) AS [Final Score], Sum([Raw Scores].[Raw Score]) AS [Total Shots]
FROM [Raw Scores]
WHERE [Round Number] = 1
GROUP BY [Raw Scores].PlayerID, [Raw Scores].[First Name], [Raw Scores].[Last Name], [Raw Scores].[Round Number]
PIVOT [Raw Scores].[Hole ID];

Note: [Raw Scores] is a query. Adding the WHERE clause with the paramter to that query yields the results perfectly fine, but when used in conjunction with the above query, it displays the error message (shown in picture above)...

Here's the [Raw Scores] Query, with the parameter which works (and has an unknown variable in it):

SELECT Scores.PlayerID, Scores.Score AS [Raw Score], Scores.[Hole ID], Holes.Par, IIf([Raw Score]=0,Holes.Par,[Raw Score]-Holes.Par) AS Score, Players.[First Name], Players.[Last Name], Scores.[Round Number], Players.Country
FROM (Scores INNER JOIN Holes ON Scores.[Hole ID] = Holes.[Hole Number]) INNER JOIN Players ON Scores.PlayerID = Players.[Player ID]
WHERE [Round Number] = [Enter Round Number:];

Update: Not defining the parameter as a field seems to fix the problem, but for readability, I still need it displaying the correct name.

TRANSFORM Sum([Raw Scores].[Raw Score]) AS SumOfScore
SELECT [Raw Scores].PlayerID, [Raw Scores].[First Name], [Raw Scores].[Last Name], [Raw Scores].[Round Number], Sum([Raw Scores].Score) AS [Final Score], Sum([Raw Scores].[Raw Score]) AS [Total Shots]
FROM [Raw Scores]
WHERE [Round Number] = ?
GROUP BY [Raw Scores].PlayerID, [Raw Scores].[First Name], [Raw Scores].[Last Name], [Raw Scores].[Round Number]
PIVOT [Raw Scores].[Hole ID];

Question:

How do I get the query to work; ask for a value?

Like so:
Enter Round Number: |

Upvotes: 0

Views: 86

Answers (2)

Timothy
Timothy

Reputation: 383

Aparently, all you have to do is nest it to clear the confusion when it checks for whether it is a field or not.

PARAMETERS [Enter Round Number:] Value;
TRANSFORM Sum([Raw Scores].[Raw Score]) AS SumOfScore
SELECT [Raw Scores].PlayerID, [Raw Scores].[First Name], [Raw Scores].[Last Name], [Raw Scores].[Round Number], Sum([Raw Scores].Score) AS [Final Score], Sum([Raw Scores].[Raw Score]) AS [Total Shots]
FROM [Raw Scores]
WHERE ((([Raw Scores].[Round Number])=[Enter Round Number:]))
GROUP BY [Raw Scores].PlayerID, [Raw Scores].[First Name], [Raw Scores].[Last Name], [Raw Scores].[Round Number]
PIVOT [Raw Scores].[Hole ID];

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

The problem is that 

WHERE [Round Number] = [Enter Round Number:]

Then it considers [Enter Round Number:] as an expression or a value, and it looks like it is neither of them and hence you are getting the error which you mentioned.

You need to provide the appropriate value to the [Enter Round Number:] expression. You can pass the value in a parameter and then you can use it. You can use QueryDefsand then create the query with parameters.

Imports Data.OleDb

dim cmd as new OleDbCommand
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT [Raw Scores].PlayerID, [Raw Scores].[First Name], [Raw Scores].[Last Name], [Raw Scores].[Round Number], Sum([Raw Scores].Score) AS [Final Score], Sum([Raw Scores].[Raw Score]) AS [Total Shots]
FROM [Raw Scores]
WHERE [RoundNumber] = ?
GROUP BY [Raw Scores].PlayerID, [Raw Scores].[First Name], [Raw Scores]. [Last Name], [Raw Scores].[Round Number]
PIVOT [Raw Scores].[Hole ID];"

cmd.Parameters.Add("@RoundNumber", OleDbType.VarChar).value = "RoundNumber"

Dim da as new OleDbDataAdapter(cmd)

Upvotes: 1

Related Questions