Deleted field rises from the grave 1

[demosoc]

I am running Crystal 9.2.0.448 against an 2003 Access DB and am pulling fields from two tables. The problem is that one of the fields (contacts.bounced?), which was removed from the report and also from the DB is now appearing as a parameter. When I open the Field Explorer to see if the field is being used (as demonstrated by the handy green check mark) I’m assured that it’s not being used in the report. Yet when I try to run the report, I get a parameter value prompt for Contacts.[Bounced?] and if I leave it null it takes longer than my lifespan to run, rendering the report useless.

When I put in a dummy field Contacts.Bounced? in the Access DB (and regardless of the data type) the report works fine and I'm able to see the SQL:

SELECT `Contacts`.`cst_member_flag`, `Contacts`.`Record`, `TOTAL_FINANCIAL`.`MinOfdate`, `TOTAL_FINANCIAL`.`MinOfInv_Trx_Date`, `TOTAL_FINANCIAL`.`MaxOfdate`, `TOTAL_FINANCIAL`.`MaxOfInv_Trx_Date`, `Contacts`.`Last Member Type`, `Contacts`.`Join Date`, `Contacts`.`cst_add_date`, `Contacts`.`Expire`, `Contacts`.`crt_certification_date`, `Contacts`.`no email_flag`, `Contacts`.`cst_eml_address_dn`, `Contacts`.`receive marketing emails`, `Contacts`.`cst_src_code`, `Contacts`.`MaxTerminate`
FROM `Contacts` `Contacts` LEFT OUTER JOIN `TOTAL_FINANCIAL` `TOTAL_FINANCIAL` ON `Contacts`.`Record`=`TOTAL_FINANCIAL`.`Record`

which clearly isn't pulling the Contacts.bounced? field.

So why is the report insisting on this field? Obviously I can just add the dummy field to the Contacts table and call it quits, but this seems messy and leaves me ignorant. So if anyone has any ideas, I’d greatly appreciate your help.

Thanks!

 

[fisheromacse]

The first two items that come to mind:
1. Is the removed field still used as a selection criteria?
or
2. as a link to a subreport?

 

[demosoc]

1. No- the only Selection Criteria is based on cst_add_date being >= a Parameter Date field
2. No- there are no subreports

 

[lbass]

Have you verified the database (database->verify database)?

-LB

 

[demosoc]

Yes, I verify the database, it recognizes that the table has changed, says it is up to date, and then I get the prompt for Contact.Bounced? as a Parameter Value when I try to run the report.

 

[NiteClerk]

Try setting your paper size to legal or larger. I normally set my page size to legal or sometimes a custom 36" so I can export vast number of columns to Excel. Anyway at some point I changed the size to letter. Whenever I ran the report the mystery fields would appear. I couldn't see them in Design view. It drove me crazy until I (randomly) changed the page size back to legal.

 

[lbass]

Parameters should appear like {?Parameter}, so exactly how does this field appear? If you are getting a prompt, then you should be checking to see where the parameter is being used, not some field.

-LB

 

[demosoc]

NiteClerk- thank you, but unfortunately, this didn't do the trick. Additionally when I look at the report explorer, the field does not appear.

--------

lbass- I am prompted in the 'Enter Parameter Values' box, under 'Parameter Fields:' it appears as 'Contacts.[Bounced?]'

--------

I created a copy of the report (ReportCopy) and got rid of all database fields except for the record number, all formula fields, all running total fields, all group name fields and all parameter fields except for the 'Contacts.[Bounced?]' field which doesn't allow delete.

When I open the select expert, it asks me to choose a field, showing absolutely no selections and no selection formula for the report or for groups.

If I 'Show SQL Query' I get:
SELECT `Contacts`.`Record`
FROM `Contacts` `Contacts`

and yet, if I refresh it prompts me for Contacts.[Bounced?].

--------

Okay- so I just deleted the TOTAL_FINANCIAL table from ReportCopy and now when I try to run it I get a 'Bad Formula Code' error.

I am confused. There are actually 4-5 reports that I'm aware of pointing to the same Access DB doing the same thing. So it's got me concerned.

 

[briangriffin]

Try exporting the report as Report Definition, then open the text file and do a search for "?".

The most likely suspect is that the parameter field is used in a formula, but you would probably remember doing that.

 

[Madawc]

The field could also be used in conditonal field formatting, or in conditional features for a section. Or record sort or group selection.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[Madawc]

It is irritating that Crystal lacks a 'Where Used' function. Is there some Third-Party Tool that would do this?

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[demosoc]

Thanks to all for the efforts so far...

Well, I've tried to export the report definition, but can't get it to work. It requires the disk (which took us awhile to find) and the one we found doesn't help even when I point it to the file it's looking for. So I'm currently unable to export the report definition.

Awhile ago the Bounced? field actually was part of a formula field that was used as a parameter. It was removed for over a month from the parameter and the report all together before the Bounced? field was deleted from the Access DB.

I've checked all the formatting and in ReportCopy there is only the one field, no grouping, no anything. Still with that Bounced? ghost. Is there anyway that Crystal is harboring the field in some hidden, back end code? Argh.

 

[IanWaterman]

YOu could try putting the Bounced field back in with a different data type.

Hopefully when Crystal comes across it it will be highlighted as wrong data type and you can kill it.

Ian

 

[demosoc]

Unfortunately, that doesn't work. I tried making the 'Bounced?' field a text and even a date/time field and the report doesn't care or highlight the field. This is the case even on the ReportCopy. So I get the prompt regardless of field type, and I still am unable to locate where the field is hidden.

 

[fisheromacse]

This is baffling for certain.

Throwing things at the wall to see what sticks...

Is there an access field that is really a formula within a field in Access that refers to the Bounced field that no longer exists? ie: we have an access table where the CName field is really a lookup to another table. If i edit the lookup table i can hose the table housing CName.

 

[demosoc]

That was it!!!

There was one little query in the Access DB that was still calling the removed 'Bounced?' field. And when I deleted the field in the Access query, the reports stopped asking for it! That's so strange as the query was never a part of the report. Crazy.

Thank you so much!