Date Initially Written: 27 January 2003.
Problem: A Microsoft Access database that used to work ok in Access 97 doesn't work after being converted to Access 2002.
Operating System: Windows XP.
Background: The database was originally created in Access 97. My client purchased a new notebook that came with Office XP and he wanted to run his old database on it. Upon opening the database in Access 2002 I was asked if I wanted to convert the database to Access 2000 format, which I elected to do. All parts of the database worked ok except for reports: all reports that had a calculated field that made use of a real field called "Shape" would show
#Error where the calculated field should have been.
One example of a calculated field's Control Source:
="Shape: " & [Shape]
=Iif([Shape] = "Round", "Round", "Other")
Resolution Steps: I knew that recursive references in reports could cause this sort of error. For example, if I had named the calculated field "Shape" then Access would not know if I was referring to the calculated field called "Shape" or the real field called "Shape". I checked the name of the field and found it to to be called "Text8", so this wasn't the problem.
I also knew that if the real field "Shape" had a value of Null then Access would also give a result of
#Error. I placed the "Shape" field on the report by itself and it printed ok.
I thought maybe the report had become corrupt, so I did a Compact & Repair; no help. I created a new report with just the calculated field on it and it still didn't work. I also created a whole new database with just one table that contained on field called "Shape" and that still didn't work.
I checked the library references but there were no missing libraries or unneeded libraries ticked.
In desperation I renamed the "Shape" field to "DiamondShape" in the table and also in the calculation on the report; it worked! Could it be that "Shape" is a reserved word in Access 2002? I checked the official Microsoft list of reserved words for Access 2002 and "Shape" doesn't appear on the list. I then went through every query, form and report and changed each reference to "Shape" to "DiamondShape". Everything is now working just fine.
Update (24 March 2019): I have since found a website that lists "Shape" as a reserved word, along with many more that don't appear on the official Microsoft list.
Recommended Reading For Microsoft Access Users: Access 2019 Bible