renaming of column in query

E

exotericist

i have a database written in 2000 which worked fine in 2002.
we've recently upgraded to 2003, and it's begun to display aberrant behaviour.

the main driving query ages work orders and sums them depending on months
aged.

it has columns of the format total-00 to total-12 where nn is the two-digit
age of the work order in month.

access 2003 is renaming the first column to total-0 when the query is
opened, breaking links both internal and external.

is this a bug? a feature? if the former is there a workaround?

i'd rather not have to alter all the reports by hand since there are
hundreds of them.
 
C

Chris O'C via AccessMonster.com

Only letters of the alphabet, numbers and the underscore are allowed in field
names. total-00 means the equation "total minus zero" to Jet. You can try
to fix bad names by enclosing them with brackets, but that can cause problems
in subqueries and in vba code and it's not guaranteed to stay fixed in future
versions of Access.

Can't say it's a bug. It's just different behavior in the newer version that
bites you when you were relying on the earlier versions to ignore the
mistakes.

Chris
Microsoft MVP
 
E

exotericist

the column names are in square brackets, where allowed, throughout.

i've always wondered at that "allowed".

the jet spec is one thing, but nearly every character is allowed if you use
square brackets.

i have a feeling i'm neither the first nor the last to complain about this.

oh well, OSFA-NS, i guess.
 
B

Bob Barrows [MVP]

exotericist said:
i have a database written in 2000 which worked fine in 2002.
we've recently upgraded to 2003, and it's begun to display aberrant
behaviour.

the main driving query ages work orders and sums them depending on
months aged.

it has columns of the format total-00 to total-12 where nn is the
two-digit age of the work order in month.

access 2003 is renaming the first column to total-0 when the query is
opened, breaking links both internal and external.

is this a bug? a feature? if the former is there a workaround?

i'd rather not have to alter all the reports by hand since there are
hundreds of them.
Click to expand...

The only workaround I have found is to switch the query to SQL View,
making sure the field name is "correct", before saving it. You will
need to remember to never save it whilst in Design View.
 
C

Chris O'C via AccessMonster.com

It's hosed. Turn off track name autocorrect and use a utility to search and
replace all the bad names so you don't have this happen again. Rick Fisher
has a good utility.

http://www.rickworld.com

Chris
Microsoft MVP
 
B

Bob Barrows [MVP]

exotericist said:
i have a database written in 2000 which worked fine in 2002.
we've recently upgraded to 2003, and it's begun to display aberrant
behaviour.

the main driving query ages work orders and sums them depending on
months aged.

it has columns of the format total-00 to total-12 where nn is the
two-digit age of the work order in month.

access 2003 is renaming the first column to total-0 when the query is
opened, breaking links both internal and external.

is this a bug? a feature? if the former is there a workaround?

i'd rather not have to alter all the reports by hand since there are
hundreds of them.
Click to expand...

Due to a suggestion from fellow MVP Tom Wickerath, I discovered that
disabling Name AutoCorrect in Tools>Options>General prevents this renaming
from occurring. You need to disable both the "Log" and "Perform" options in
that section.
 
B

Bob Barrows [MVP]

Bob said:
Due to a suggestion from fellow MVP Tom Wickerath, I discovered that
disabling Name AutoCorrect in Tools>Options>General prevents this
renaming from occurring. You need to disable both the "Log" and
"Perform" options in that section.
Click to expand...

I've just been informed that a Compact/Repair is necessary after doing this.
 
E

exotericist

is the "Name AutoCorrect" feature broken?

or is it working correctly given the non-standard column names?

or is it working as originally spec'ed, but you forgot about backward
compatibility and it has to be rethought?
 
Top