Access 2007 Needs UNEEDED Parameter!

[Kila]

Our facility just updated MS Office from 2003 to 2007. I have an inherited database that has a new problem. There is a clumsy but functional report that consists of 57 subreports in the header section of an otherwise unaltered report. There is only the title, a date() field, a page number field, and the 57 subreports arranged in rows.

Each subreport is attached to a crosstab query that generates a small "table"...usually 3 columns by 4 to 10 rows.

The problem is this, since Office was updated, the report keeps looking for the field:

CAdatefilteredPHS6/03Query.PatientLastName (from the query most of the crosstabs are based on)
or
Coding Abstraction.PatientLastName (from the table the query above is based on)

...in an "Enter Parameter Value" dialog.

I know, TERRIBLE nomenclature! (I inherited this, remember!) But it used to work fine. With such a basic report and 57 subreports to fix, I left well enough alone! The strange thing is:
1. While the field PatientLastName IS in the query underlying the report, there is nothing in the report "looking" at it.

2. In the query AND in all the queries in the subreports, nowhere is there anywhere where there is a parameter to search for in PatientLastName. The condition for that field is blank in every query.

3. The field, PatientLastName is not even used in any of the crosstab queries.

4. All of the queries for each subreport run just fine if you run them seperately.

5. The "Enter Parameter Value" prompt comes up about 12 times, DEFINITELY less than 57.

6. There is NO SQL or macros buried behind the scenes.

Any suggestions? Thanks in advance!!!


BTW... Here is the SQL for the query:

SELECT [Coding Abstraction].DischargeDate, [Coding Abstraction].PatientLastName, [Coding Abstraction].RecordStarted, [Coding Abstraction].PatientSSN, [Coding Abstraction].Attending, [Coding Abstraction].Service, [Coding Abstraction].Informed_Consent_Pro, [Coding Abstraction].[509 IC-Proc 1], [Coding Abstraction].[Pro1 sf 522], [Coding Abstraction].[Imed IC proc 1], [Coding Abstraction].[Service 2], [Coding Abstraction].[509 IC-Proc 2], [Coding Abstraction].[Pro 2 sf 522], [Coding Abstraction].[Imed IC proc 2], [Coding Abstraction].Inform_Consent_Note, [Coding Abstraction].[StaffConcurW/I24Hours], [Coding Abstraction].StaffNoteatchange, [Coding Abstraction].[Significant Chg W/Reassessment], [Coding Abstraction].Surgery, [Coding Abstraction].[Surg Treating Specialty ID], [Coding Abstraction].[509 IC-Proc surg], [Coding Abstraction].[surg sf 522], [Coding Abstraction].[Imed IC proc surg], [Coding Abstraction].Surg_Pre_OP_Note, [Coding Abstraction].[Name of Surgeon & Assistant], *
FROM [Coding Abstraction]
WHERE ((([Coding Abstraction].DischargeDate) Between #6/1/2009# And #6/30/2009#));

 

[Atomic Shrimp]

I think your problem is caused by the fact that the column names in crosstabs are dependent on the data - when you create a report based on a crosstab query, it uses the explicitly-named columns. Next time you run the thing, using different data, or criteria, the column names are different and the report can't find the fields it wants.

There are some tutorials somewhere on this board about 'dynamic crosstab reporting' that should help...

 

[Kila]

Thanks for your reply. I'm confused. Why is it an issue? None of the crosstabs utilize the PatientLastName field, only the counts that are in the other fields that have nothing to do with the patient's names (they are all based on ward or clinic name data). Even if I totally remove the PatientLastName field, the original query results are the same. It is irrelevant & I am not sure why the original designer even included it in the query other than maybe they just included ALL the fields that were in the table.

The results of the 57 subreports all look similar to this below (with bogus data). No PatientLastName in sight!

Treating Specialty CountOfTreating Specialty Yes No
Med 25 23 2
Surg 19 19
MH 22 22
Neur 15 14 1


This particular one has the following SQL:
SELECT [CAplanPH8/12/03Query_Crosstab].[Treating Specialty ID], [CAplanPH8/12/03Query_Crosstab].[CountOfTreating Specialty ID], [CAplanPH8/12/03Query_Crosstab].[Yes], [CAplanPH8/12/03Query_Crosstab].[No]
FROM [CAplanPH8/12/03Query_Crosstab];


The crosstab that is referenced has this SQL:
TRANSFORM Count([CAdatefilteredPH5/6/03Query].[24 hr Function Screen]) AS [CountOf24 hr Function Screen]
SELECT [CAdatefilteredPH5/6/03Query].[Treating Specialty ID], Count([CAdatefilteredPH5/6/03Query].[Treating Specialty ID]) AS [CountOfTreating Specialty ID]
FROM [CAdatefilteredPH5/6/03Query]
GROUP BY [CAdatefilteredPH5/6/03Query].[Treating Specialty ID]
PIVOT [CAdatefilteredPH5/6/03Query].[24 hr Function Screen] In (Yes,No);

 

[Atomic Shrimp]

What do the results of that crosstab query actually look like? What are the column names in the raw crosstab query results? (or was that the example data you posted?)

Are there any cases where a whole column is missing from a crosstab result set (because there happens to be no data to summarise into it)?

 

[Kila]

Interestingly, I just tried to print the thing. It went through the errors AGAIN, and then, it said it could not open any more tables, and THEN, it gave error messages that the Recordsource for six of the crosstab queries did not exist. THEN it gave the original error messages a few more times, then it printed just fine (I will have to check & see if it is fine). At least it spits out the report, but what is with all the error messages if they do not amount to anything?

Thanks for your help!

 

[Kila]

That was it. I had lined it up with spaces, but they were removed when I posted. 5 rows, 4 columns.

 

[Kila]

I will have to look at the columns for those with NO date. It is time to go so I will look into it tomorrow. Thanks!!!

 

[Atomic Shrimp]

One potentially productive way to troubleshoot this:

Run the report - when it prompts you for a parameter, enter a value that would never normally appear on the report (I favour the word 'banana' for this purpose).

Scrutinise the report output for instances of the entered value/word. Note their location on the report.

Open the report in design view, find the field where you saw the value/word and find out where it's trying to take its data from. Scrutinise the record source for the report (or that particular subreport) and see if the control is asking for something that isn't in the record source.

 

[Kila]

Will do. Thanks! I'll report what I find.

Here is the sample table again. Maybe it will like periods better than spaces.

Treating Specialty.....CountOfTreating Specialty....Yes....No
Med........................25....................................23.....2
Surg.......................19....................................19
MH.........................22....................................22
Neur.......................15....................................14......1

 

[Atomic Shrimp]

Will do. Thanks! I'll report what I find.

Here is the sample table again. Maybe it will like periods better than spaces.

Treating Specialty.....CountOfTreating Specialty....Yes....No
Med........................25....................................23.....2
Surg.......................19....................................19
MH.........................22....................................22
Neur.......................15....................................14......1

Is it possible that in some of these result sets, there aren't any 'No's at all? (if so, the whole column will probably not be there - if it's a crosstab column header.

 

[Kila]

I *FINALLY* got back around to this. I tried your banana trick. The word BANANA did not show up ANYWHERE in the printed report. Any other suggestions would be greatly appreciated. Thanks!!!

BTW...the report WILL let you hit cancel...cancel...cancel to skip the LastName field when you run it. Putting something IN there apparently is unnecessary.