Enter Parameter Value (1 Viewer)

[diversoln]

I set up a query used to create a report. In order to display the data in a desirable format, I've defined a new field in the query and perform some simple functions. When I go to run the query, I receive a message relating to the new field "Enter Parameter Value" where its allowing me to enter the value. I just press enter and the query runs fine so it doesn't appear to use the value its prompting for.

For example I have the following fields set up in the Query Design view, the first two from my table, [PATIENT] is a "Lastname, Firstname" format the last field is created to manipulate the patient name to provide only the patient's last name:

[CASE] [PATIENT] LASTNAME: Left([PATIENT],InStr([PATIENT],',')-1)

So why is it prompting me for this and how can I avoid it ?

 

[Pat Hartman]

You can't use [CASE] [PATIENT] LASTNAME as the name of a column. Access needs the square brackets to surround poorly formatted names so you can't create a name that includes them. Change the name to CASE_PATIENT_LASTNAME or CASE PATIENT LASTNAME if you insist on the poor practice of using embedded spaces.

 

[diversoln]

Pat, there are three fields in my query:

1.) [Case]
2.) [Patient]
3) [Lastname] >> Calculated from a function of [Patient] which is of the format of "Lastname, Firstname".

Sorry my orginal post must have been confusing. I had several spaces between each of the fields to indicate more clearly that there were three disctinct fields, but when it posted it wiped out the spaces. Anyway, any idea why the request for a parameter value is coming up ?


Thanks.

 

[Pat Hartman]

It is really hard to find syntax errors when I don't have the actual code to look at. Please post the actual SQL rather than a summary of it.

 

[diversoln]

Here's the SQL:

SELECT DISTINCTROW APWorklistTable.CASE, APWorklistTable.PATIENT, UCase(Left(Left([PATIENT],InStr([PATIENT],',')-1),10)) AS LASTNAME, APWorklistTable.SPECIMEN, APWorklistTable.BLOCK, Max(APWorklistTable.BLOCK_NUM) AS [Max Of BLOCK_NUM], IIf([BLOCK]="A",[CASE],"") AS SHOWCASE, IIf([BLOCK]="A",[LASTNAME],"") AS SHOWPAT
FROM APWorklistTable
GROUP BY APWorklistTable.CASE, APWorklistTable.PATIENT, UCase(Left(Left([PATIENT],InStr([PATIENT],',')-1),10)), APWorklistTable.SPECIMEN, APWorklistTable.BLOCK, IIf([BLOCK]="A",[CASE],""), IIf([BLOCK]="A",[LASTNAME],"");


The formatting has evolved a bit since last I posted but the problem with the requested parameter value for [LASTNAME] still persists.

The intentions of the query are to:

1. Prepare data for display in a report.
2. Determine the maximum number of specimen blocks submitted for each specimen for each case.
3. Format the patient name so that only the patient's Lastname is displayed on the report and at the same time, limit the Lastname to 10 characters.
4. Only show the case number and patient name once for each case on the report - when the Block = "A".

The only problem is that when the query runs, the "Enter Parameter Value" for the LASTNAME prompt box appears. The prompt appears just one time during the running of the query, not for each record. I press enter and the query and report run great. I've tried entering characters also - nothing changes - the report still runs.


Thanks for your help!

 

[Jon K]

Replace [LASTNAME] in the Group By clause with its expression. You can't use field alias in a Group By clause.

 

[diversoln]

Jon,

Thanks for the solution. It worked perfectly. Since I was using the Access Design View to set up my query, and Access generated the SQL code, does this indicate that there is a glitch in the Access SQL writing routine ?

Thanks again for your input !!

Cindy

 

[Jon K]

The syntax of SQL statements is rather inflexible.

We can only remind ourselves, no matter whether we are building a query in Design View or SQL View, that we can't sort by, order by, group by or set a criterion for an expression that contains a field alias unless we replace the field alias with its expression.