Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why am I getting “Enter Parameter Value” when running my MS Access query?

SELECT ID, 
       Name, 
       (SELECT CityName 
        FROM City 
        WHERE Employee.CityID = City.CityID) AS [City Name] 
FROM Employee 
WHERE [City Name] = "New York"

I'm about selecting all employees who come New York but whenever I run the query, I always get a “Enter Parameter Value” box. How can I fix this?

like image 828
Teiv Avatar asked Nov 20 '10 12:11

Teiv


People also ask

How do I remove enter parameter value in Access?

Answer: To remove all parameters from a query, open your query in Design view. Then under the Query menu, select Parameters. When the Query Parameters window appears, highlight the Parameter name and press the Delete key.

What is parameter value?

Parameter values can represent data from a document field, the current system date/time, a static value, the result of a connector call or database query, or a variety of other values.


1 Answers

This is because Access does not allow you to use field aliases in the query - it does not recognize [City Name] as a valid field name. Aliases are only used as field names in the result set. Rather, you need to use the entire expression.

As such, this query would probably be more easily defined in Access as:

SELECT ID, 
       Name, 
       CityName AS [City Name]
FROM Employee INNER JOIN City
    ON Employee.CityID=City.CityID
WHERE CityName = "New York"

Also, 'Name' is a reserved word - using it as a field name is not suggested.

like image 130
Remi Despres-Smyth Avatar answered Oct 17 '22 22:10

Remi Despres-Smyth