CODEWITHSUNDEEP
sqlms-accessms-access-2007
thornomad
thornomad

Reputation: 6777

"Enter Parameter Value" Prompt for Unreferenced Parameter

In MS Access 2007 I occasionally come across this issue: A query will start to produce a "Enter Parameter Value" prompt -- only the prompt is for a field that I do not reference in my SQL.

Example (query name: qryOPI_ReportingLocation):

SELECT CODES.ReportingLocation, DateValue(OPI.EncDateTime) AS ApptDate, OPI.MedRec, OPI.AccessCode
FROM [OPI DATA] AS OPI LEFT JOIN [Access Codes] AS CODES ON OPI.AccessCode=CODES.Code
WHERE (IsNumeric(MedRec)) And (OPI.EncDateTime>=#10/1/2013#) AND (OPI.EncDateTime<#10/1/2014#);

Prompt:

Enter Parameter Value: qryOPI_ReportingLocation.EncDate

Why am I being asked for this parameter? If I hit Cancel, the query fails to run. If I enter any value (for example: bob) the query will run correctly.

My Solution So Far:

What's really frustrating, is if I take that same SQL, paste it into a newly created query it runs without a hitch. I then delete the old query and rename the new one until next time.

Is there a better way?

Upvotes: 1

Views: 112

Answers (2)

Newd
Newd

Reputation: 2185

Generally any time that I have run into this there is a remnant Order By field left in the query. Something that was set before and the SQL was changed but it stuck around.

Put the query into SQL view, then on the right in the Property Sheet remove anything in the Order By property and the error should go away.

The second possibly is just a field name that has been misspelled, however since this looks like it was created using Query Design that is a little less likely.

Upvotes: 2

Greg Viers
Greg Viers

Reputation: 3523

This is usually caused by misspelling a field name. If you spell your field name even slightly different than it is spelled in the query or table definition, Access treats it as a parameter.

Upvotes: 0

Related Questions